Display Missing invoice numbers from a sequence.

[lrxc]

Can anyone tell me how to create a formula that will take a column of
invoices (just a numerical value like 108007) and display back the missing
numbers from that sequence?

 

[Bernie Deitrick]

Array enter a formula like this (enter using Ctrl-Shift-Enter instead of just Enter) next to the
first value

=IF(ROW(A1)>(MAX(A:A)-MIN(A:A)),"Enough",IF(ISERROR(MATCH(MIN(A:A)+ROW(A1),A:A,FALSE)),MIN(A:A)+ROW(A1),""))

and copy down until the formula returns "Enough". You could then copy and paste values somewhere
else, and then sort to get rid of the cells that returned "".


HTH,
Bernie
MS Excel MVP

 

[Max]

Assume you are checking for 100 sequential invoice numbers from 108000 to
108099. Assume source data (your invoice numbers) is running in D1 down and
are all real numbers

In E1:
=IF(ISNUMBER(MATCH(108000+ROWS($1:1)-1,D,0)),"",108000+ROWS($1:1)-1)
This is the criteria col. It embedds the 1st invoice number involved the
series (108000). Modify the 1st invoice number to suit what you have.

In F1: =IF(ROWS($1:1)>COUNT(E:E),"",SMALL(E:E,ROWS($1:1)))
This is the results col. It'll "float up" all the results neatly at the top

Copy E1:F1 down to F100 (ie copy down by the number of invoice numbers
involved). The missing invoice numbers will appear in col F, neatly packed at
the top. Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik