Break in sequence ?

[chilidog808]

I was posed the question,
"i have a listing of 1000's of sequencial numbers that i have sorted in asending order and I want to find if any numbers are missing in the sequence"

Does anyone know a way this is possible?

 

[just_jon]

Welcome to MrExcel - I hope this is not homework?

One method that flags missing data, assuming data in column A starting in A1, in B2 copied down --

=IF(A2<>A1+1,"Missing at least "A1+1,"")

 

[Peter_SSs]

chilidog808

Welcome to the Mr Excel board!

If you don't need to know what is missing but just IF something is missing, you might be able to use/adapt this:

Formula in D1:
=IF(LOOKUP(9.99999999999999E+307,A:A)-A1+1=COUNT(A:A),"no","yes")

Mr Excel.xls
	A	B	C	D	E
1	5		Missing numbers?	yes
2	6
3	7
4	9
5	10
6	12
7	13
8
Missing Numbers

 

[West Man]

Assuming your sequential numbers start in A1, in row 2 of an unused column type =if(a2>a1+1,"Missing","") Drag this down to your last row.Your request did not reference the possibility for duplicates so no check is made with this formula.

 

[Oaktree]

If your numbers are in column A,

B1 = MIN(A:A) to find the smallest number in your range
B2 = MAX(A:A) to find the largest number in your range
B3 = SUMPRODUCT(--ISNA(MATCH(ROW(INDIRECT(B1&":"&B2)),A:A,0))) will tell you the number of missing values between B1 and B2

 

[chilidog808]

Thank you.. works great.