Need help in excel

[smilydil]

Salaam,

Guys I am encountering a problem and looking for the solution. I am sure that I will get the positive reply from you.
The problem is that I have a different series of numbers in excel sheet and would like to find a number in the whole series that is missing in the whole series. Here I will explain my question in an example.

The series is as under.
1001
1002
1003
1005
1006
1007
1009
1012
1013
As you can see that, the series not ascending continuously. Some digits are missing like 1004, 1008, 1010, and 1011.
Now my question is that how can I trace the same thing with the help of a formula. Mean I would like to use a formula to check that what digits are not showing in the column.

Please note that original series is over 900 pages.

[syar]

as per my understanding

I assume aap ka data column A m ha aur a1 sa start hota ha.

In B1 put the below formula it will show how many numbers are missing. Adjust ranges/references accordingly.

=(MAX(A:A)-MIN(A:A)+1)-MATCH(9.999999E+30,A:A)-(CELL("row",A1)-1)

The below procedure will highligh the cell where the series is missing
1. choose your range
2. In home tab--> select conditional Formatting
3. Conditional formatting----->New Rules--->Formula to determine which cell to highlight
4. put the below formula

=NOT(A2=A1+1)

5. then choose your desire cell formatting.
This will highlight the cell where series is broken or missing.

If you want to know which number is missing put below array formula
in C1 or choose your output cells and copy down as far as required.

=SMALL(IF(COUNTIF(A:A,ROW($1:$30))=0,ROW($1:$30),""),ROWS(C1:$C$1))

Remember Array formula must be enter with the combination of SHIFT+CONTROL+ENTER. and array formulas are very costly.

regards

[Net-Rider]

تھریڈ کو موو کیا گیا کورس سیکشن سے آسک اینڈ ایکسپرٹ سیکشن میں