Break in sequence ?

chilidog808

New Member
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?

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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,"")

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
ABCDE
15Missing numbers?yes
26
37
49
510
612
713
8
Missing Numbers

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.

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

Thank you.. works great.

Replies
2
Views
108
Replies
7
Views
204
Replies
1
Views
106
Replies
7
Views
657
Replies
3
Views
283

1,219,993
Messages
6,151,354
Members
451,021
Latest member
alejandrolima

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back