Iterative calc (?): checking whats the lowest txt+number a range doesnt have

[deLockloire]

hi,

I have a table, and one of the columns contains text values (invoice numbers, which may contain both alphabetical and numerical characters). Now, the column contains the number of both our invoices and our partners' invoices. What I'd like to do is check if any of OUR invoice number is missing. What needs to be known?

1) Our own invoice numbers starts with XG and then comes a number, e.g., 00001. I.e., XG00001. This is the first invoice. The second invoice is XG00002, the third is XG00003, etc. So the invoice number is increased by one with each new invoice issued.

What I'd like to do, is somehow give our last invoice number (e.g., XG05613), and a formula in a cell should check if we have any invoice number (from XG00001 to XG05613) missing from the given column. And it should write the smallest number of invoice number which is missing.

For instance, if XG01000 and XG02000 is nowhere to be found in column C, it should write XG01000. If all invoices are found between the given range (XG00001 and XG05613), it writes OK or something.

I feel it should somehow be solved with iterative calculations, but I'm not sure how. Can you help with this?

 

[deLockloire]

no ideas?

Oh, yeah, I forgot to mention that the invoice numbers in column C are NOT in numerical order!

 

[jasonb75]

You could do it with iterative calculation, but it would be better to do it without.

A1:A44 refers to the list of invoice numbers.

="XG"&TEXT(MATCH(0,COUNTIF(A1:A44,"XG"&TEXT(ROW(INDIRECT("1:"&MAX(IF(LEFT(A1:A44,2)="XG",--RIGHT(A1:A44,5))))),"00000")),0),"00000")

Note this is an array formula and must be confirmed with Shift Ctrl Enter.

 

[deLockloire]

Hey, thanks, however, it doesnt seem to work... could you explain shortly? (I did enter it as an array formula.)

 

[jasonb75]

Did you edit the ranges to match your real data?

Are your invoice numbers consistant in length? The formula is based on them being
"XG" followed by a 5 digit number.

Note it will show a #N/A error if no invoices are missing.

Which version of excel are you using?

 

[deLockloire]

Yeah, I did. I use an excel table, so i gave the table name and the column name for the range.

Our invoices have the constant character length, starting w/ XG, then a five digit number (filled up w zeroes and starting from 1), but the range has other invoice numbers, which do vary in legth.

On occasions, two or more rows can contain the same invoice number (if that matters).

The invoce numbers are not in A-Z order in the range. So it is possible that in the first row you can find XG05423 and in the second row you can find XG00015.

I'm using Office 2007.

I tried to delete one invoice number in case we have everything in place, but the formula didn't work. Also, I cannnot see where can I give the current invoice number (i.e., the maximum value the formula should search for). But to be honest, I don't really understand the formula yet.

 

[jasonb75]

As far as I can see the formula should work, unless something is not compatible with the excel table.

I forgot to ask what result you are getting from the formula, is it giving an error or just a wrong result?

Try =MAX(IF(LEFT(A1:A44,2)="XG",--RIGHT(A1:A44,5)))

Array confirmed, this should return the max invoice number in the list.

Also as a test for problem entries, try =SUMPRODUCT(--(LEFT(A1:A44,2)="XG"),--(LEN(A1:A44)<>7)) as a non-array formula. (I'm hoping the result of that one is 0)

 

[deLockloire]

The =MAX... array formula gives a VALUE# error, but the sumproduct formula has shown that there are 10 invoices which do contain XG which are not ours. However, all 7-character-long invoices that does contain XG are, indeed, ours (and this is unlikely to change).

 

[jasonb75]

Think this should work

=IFERROR("XG"&TEXT(MATCH(0,COUNTIF(A2:A45,"XG"&TEXT(ROW(INDIRECT("1:"&MAX(IF(LEFT(A2:A45,2)="XG",IF(ISNUMBER(--RIGHT(A2:A45,5)),--RIGHT(A2:A45,5)))))),"00000")),0),"00000"),"No Missing Invoices")

 

[deLockloire]

I forgot to ask what result you are getting from the formula, is it giving an error or just a wrong result?

it gives N/A# error regardless of the missing invoice number(s)