how to find skipped numbers in excel

rodynetwork
rodynetwork used Ask the Experts™
on
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.
Comment
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.

Author

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

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

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

See attached example.

Book1.xls
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:
=c2-c1

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.

Author

Commented:
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

Author

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

21024      
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
21046      
21048      Missing 21045
21049      Missing 21046
21050      
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  **

Author

Commented:
Ok. Here is a sample worksheet.
allPayments-1-.xls
TracyVBA Developer

Commented:
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.


=IF(C3-C2=1,"","Missing")
TracyVBA Developer
Commented:
OK, based on your sample file, try this formula:

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

See attached.

allPayments-1.xls

Author

Commented:
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
Commented:
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.
MISSING-NUMBERS.xls

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