Finding missing values in a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Back
Top