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.
Code:
=(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
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.
Code:
=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
Bookmarks