Find Non-consecutive numbers in list

[Ramona]

Hi,

This is a fairly simple question that I was trying to do
without creating a macro, but I think I will have to do
it. I have a list of items in numerical order, I need to
find any gaps in the Invoice # list and flag the number to
a new worksheet.

Invoice# Customer
1001 Acme
1002 Davis
1004 Shopstar
1005 Walker
1008 Trains

I would need the list to show that I'm missing Invoice #s
1003, 1006, and 1007. Thanks for any help.

 

[ElsiePOA]

One Way -

Assuming your list of invoice numbers is in cells A1:A1000 on Sheet
and that they are sorted in numerical order -

On Sheet2 enter the following formula in A2
=IF(SHEET1!A2=SHEET1!A1+1,"",SHEET1!A1+1).

Copy this formula down to cells A3:A1000 on SHEET2 and you will have
list of all missing invoice numbers with the other cells all blank. I
you want to remove all of the blank cells, just sort A2:A1000 i
ascending order and your list will be in consecutive cells at the to
of the column