If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Display Missing invoice numbers from a sequence.
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? |
#2
|
|||
|
|||
Display Missing invoice numbers from a sequence.
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(A 1),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 "lrxc" wrote in message ... 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? |
#3
|
|||
|
|||
Display Missing invoice numbers from a sequence.
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 --- "lrxc" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|