how to find skipped numbers in excel

rodynetwork used Ask the Experts™
I have an excel sheet where invoice numbers are in column C.  How can I find instances of skipped numbers in that column?  For example, invoice numbers run consecutively until invoice 23488, then skips to 23490. 23489 is missing.  I need to find this occurance throughout the excel sheet.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you are able to add a column to it, you can make a simple formula to check them...
column d:  =c2-c1
copy it down the entire column and anything with a number greater than 1 has a skip.  you can also add in "conditional formatting" to make it easier to see them.
You may be able to do it with conditional formatting alone, though I haven't tried a formula as the condition rule.


I can add a column. Once I add an extra column, where to do I place the formula? Idiot proof instructions appreciated.
TracyVBA Developer

Insert a new column into D and put this in D3 and drag down:

=IF(C3-C2=1,"","Missing " & C3-1)

See attached example.

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

in that extra column is where you place the formula.

in row 2 of the new column enter:

copy that cell.  select the rest of the cells in the column and paste the formula.

the conditional formatting is more involved and you have to go through the wizard in excel to do that piece, but it would let you turn any cells in your new column red if they were greater than 1.


expert02232010, something seems off.  Here is what I get:

23480      0
23481      0
23482      0
23483      1
23484      0
23485      0
23486      0
23487      1
23488      0
23490      0
23491      0
23493      0


broome9, here is what I am getting.  What am I doing wrong?

21027      Missing 21023
21028      Missing 21023
21029      Missing 21026
21030      Missing 21027
21031      Missing 21028
21042      Missing 21029
21043      Missing 21030
21044      Missing 21042
21045      Missing 21042
21048      Missing 21045
21049      Missing 21046
21051      Missing 21048
      Missing 21049
21053      Missing -1
21055      Missing 21052
21057      Missing 21053
21058      Missing 21056
21060      Missing 21057
21062      Missing 21059
Rody, I think it might be beneficial if you provide a sample workbook.
Please tell me what formula is in the marked cells:
23482      0
23483      1  **
23484      0
23485      0
23486      0
23487      1 **
23488      0
23490      0  **


Ok. Here is a sample worksheet.
TracyVBA Developer

I assumed you would have 1 missing number, but appears you have multiple, so use this formula, and where column D says "Missing", it means that that cell and the cell above are not sequential.

TracyVBA Developer
OK, based on your sample file, try this formula:


See attached.



Thanks for this broomee9.  It doesn't seem to be working. I am pasting =IF(OR(C3-C2=1,C3=C2),"","Missing") into D2 and dragging it down.  Nothing happens..  Cells are just blank.
Try typing that formula in manually into D3 then copy that cell, and select the rest of them and paste.  It could be treating it as text instead of a formula if you pasted it from here.
VBA Developer
It needs to be pasted in D3 and dragged down as I already stated in my original post, because you can't compare the first one.  If it's blank then that means there's no gap.  If it says missing, then there's a gap.  Did you look at the attached workbook I provided?  It's working in there.
Dear Rody,

Please find the attached file hope it will work for you.

COPY & PASTE all your invoice numbers in column A of the attached file and hit the command button.

Do remember to increase the upper limit in the code.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial