Finding missing values in a column
 

I'm looking for a way to automaticaly find missing values in a column of
numbers. Here's an example (all values are in the same column):

50
51
52
54
55

Value 53 is missing from the column (which should be increasing by 1 every
row). Is there a way to automatically detect that there is a gap between 2
values?

Thanks

Finding missing values in a column
 

With your list of contiguous source numbers beginning anywhere in Col_A

Example:
50
51
52
54
55


This regular formula returns the 1st missing value
B1:
=SMALL(INDEX((COUNTIF(A:A,ROW(INDIRECT(MIN(A:A)&": "&MAX(A:A))))=0)*ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A ))),0),ROW()+COUNTA($A:$A))
Copy that formula down until you receive an error value.

Using the above data, B1 returns 53

Is that something you can work with?
Post back with more questions.
----------------------
Regards,

Ron
Microsoft MVP (Excel)



"larry brunster" <larry wrote in message
...
I'm looking for a way to automaticaly find missing values in a column of
numbers. Here's an example (all values are in the same column):

50
51
52
54
55

Value 53 is missing from the column (which should be increasing by 1 every
row). Is there a way to automatically detect that there is a gap between 2
values?

Thanks