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]

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")

This one works. Slow as hell, even on a core i7, but it works! Big thanks. also, I'm beginning to decypher the formula.

 

[jasonb75]

I think anything will be slow with this type of calculation, mainly due to the amount of cross calculation.

Testing on 5000 rows of data, this seems a little faster, but the delay is still there.

=IFERROR("XG"&TEXT(MATCH(FALSE,ISNUMBER(MATCH("XG"&TEXT(ROW(INDIRECT("1:"&MAX(IF(LEFT(A2:A5001,2)="XG",IF(ISNUMBER(--RIGHT(A2:A5001,5)),--RIGHT(A2:A5001,5)))))),"00000"),A2:A5001,0)),0),"00000"),"No missing invoice")

Still needs to be array confirmed.

 

[deLockloire]

Could you please help me understand the first half of the formula? (I understand that the MAX formula searches for the largest number. But what does the INDIRECT, ROW, COUNTIF and MATCH do there?

 

[jasonb75]

Starting from the middle,

("1:"&MAX(...)) returns a range of rows as text, Indirect turns that text string into a valid range.

Row() then uses that to produce an array of numbers {1,2,3,4,etc...} up to the max invoice number.

"XG"&TEXT(... then convert that array of numbers to an array of invoice numbers {"XF00001","XF00002",etc...}

In the first formula, COUNTIF then counts the number of times each invoice appears in the list, missing ones will have a count of 0 which is what Match(0, is looking for.

In the second formula, the second Match searches for each invoice number in the table, if it is found then match returns that position (row number, relative to the table), if not it returns an error.

Isnumber converts numbers to true and errors to false, the first match then uses the false result to find the missing invoice number.

 

[deLockloire]

("1:"&MAX(...)) returns a range of rows as text, Indirect turns that text string into a valid range.

So, if the last invoice number is XG06166, then it should give 1:6166 as a value, right? But how does it become a range of rows instead of range of invoice numbers?

Row() then uses that to produce an array of numbers {1,2,3,4,etc...} up to the max invoice number.

You mean that it doesnt give you the row number (as the primary function of ROW() should), but it produces an array of numbers from the invoice numbers? Or this is what gives you the row number of invoice# 1 and the row number of invoice# MAX and creates an array from that?

In the first formula, COUNTIF then counts the number of times each invoice appears in the list, missing ones will have a count of 0 which is what Match(0, is looking for.

Aha! I see. But... MATCH() needs a lookup array. Where is that?

 

[jasonb75]

In answer to the first 2 questions, concatenation, in an array formula, ROW(1:3) would return {1,2,3}**, meaning ="XG"&TEXT(ROW(1:3),"00000") would return {"XG00001","XG00002","XG00003"}.

** ROW(INDIRECT(x:y)), as far as I know, is the only way to create a sequential (dynamic) array within a formula, you can do it with just ROW(x:y), but only if the array is static.

The lookup array is in countif, the criteria is an array, so the result will also be an array, which match makes use of.

 

[deLockloire]

In answer to the first 2 questions, concatenation, in an array formula, ROW(1:3) would return {1,2,3}**, meaning ="XG"&TEXT(ROW(1:3),"00000") would return {"XG00001","XG00002","XG00003"}.

** ROW(INDIRECT(x:y)), as far as I know, is the only way to create a sequential (dynamic) array within a formula, you can do it with just ROW(x:y), but only if the array is static.

I see. This is a cool function. I never knew it could be done w/ row().

The lookup array is in countif, the criteria is an array, so the result will also be an array, which match makes use of.

Oh, I see! So the array is not an actual column/row array, but the array of invoice numbers! I like this!

Thanks for taking the time to explain!

 

[jasonb75]

If you want to see how a formula like that is working, set it to a small range, say between 5 and 10 rows, then follow the calculation step by step using the formula evaluation. (keyboard shortcut Alt M V).

I've been trying to figure out a more economic way to make this work and think I might have done it. (if you can use the UDF).

First copy / paste the code to a regular VBA module (Alt F11, insert, module).

After copying the code, close the editor.

Option Explicit
Public Function XGCHECK(ByVal rng As Range, Optional startafter As Variant, Optional lastinv As Variant) As String
Dim mCell As Range, a As Long, x As Long, FindInvoice, FoundMissing
If IsMissing(startafter) Then
    startafter = 0
Else
    startafter = Right(startafter, 5)
End If
If IsMissing(lastinv) Then
    For Each mCell In rng
        If Left(mCell, 2) = "XG" And IsNumeric(Right(mCell, 5)) Then
            If CLng(Right(mCell, 5)) > CLng(lastinv) Then lastinv = CLng(Right(mCell, 5))
        End If
    Next
Else
    lastinv = Right(lastinv, 5)
End If
For x = CLng(startafter) + 1 To CLng(lastinv)
    On Error GoTo FoundMissing
       FindInvoice = Application.WorksheetFunction.Match("XG" & Format(x, "00000"), rng, 0)
Next
    XGCHECK = "No missing Invoice"
Exit Function
FoundMissing:
    XGCHECK = "XG" & Format(x, "00000")
End Function

Then choose a formula to use (edit ranges to suit)

To check all invoice numbers from 1 to max use =XGCHECK(A2:A10001)

To check a specified range of invoices use =XGCHECK(A2:A10001,start,end)

Note, start and end are both optional, starting at XG01000 will ignore XG01000 and actaully start at XG01001 (this was deliberate so you can use the one missing invoice number as the start to find the next).

End is the last invoice number that will be checked (to check all up to XG01000 use either =XGCHECK(A2:A10001,,"XG01000") or =XGCHECK(A2:A10001,0,1000) or =XGCHECK(A2:A10001,,B1) (where b1 holds an invoice number in either format).

To check after a specific invoice up to the max, =XGCHECK(A2:A10001,B1)

Hopefully that's enough examples for you to see all possible formats.

It's not instant (unless the first missing invoice is close to the start invoice number), but seems far quicker than the array formula.

 

[deLockloire]

If you want to see how a formula like that is working, set it to a small range, say between 5 and 10 rows, then follow the calculation step by step using the formula evaluation. (keyboard shortcut Alt M V).

I've been trying to figure out a more economic way to make this work and think I might have done it. (if you can use the UDF).

First copy / paste the code to a regular VBA module (Alt F11, insert, module).

After copying the code, close the editor.

Option Explicit
Public Function XGCHECK(ByVal rng As Range, Optional startafter As Variant, Optional lastinv As Variant) As String
Dim mCell As Range, a As Long, x As Long, FindInvoice, FoundMissing
If IsMissing(startafter) Then
    startafter = 0
Else
    startafter = Right(startafter, 5)
End If
If IsMissing(lastinv) Then
    For Each mCell In rng
        If Left(mCell, 2) = "XG" And IsNumeric(Right(mCell, 5)) Then
            If CLng(Right(mCell, 5)) > CLng(lastinv) Then lastinv = CLng(Right(mCell, 5))
        End If
    Next
Else
    lastinv = Right(lastinv, 5)
End If
For x = CLng(startafter) + 1 To CLng(lastinv)
    On Error GoTo FoundMissing
       FindInvoice = Application.WorksheetFunction.Match("XG" & Format(x, "00000"), rng, 0)
Next
    XGCHECK = "No missing Invoice"
Exit Function
FoundMissing:
    XGCHECK = "XG" & Format(x, "00000")
End Function

Then choose a formula to use (edit ranges to suit)

To check all invoice numbers from 1 to max use =XGCHECK(A2:A10001)

To check a specified range of invoices use =XGCHECK(A2:A10001,start,end)

Note, start and end are both optional, starting at XG01000 will ignore XG01000 and actaully start at XG01001 (this was deliberate so you can use the one missing invoice number as the start to find the next).

End is the last invoice number that will be checked (to check all up to XG01000 use either =XGCHECK(A2:A10001,,"XG01000") or =XGCHECK(A2:A10001,0,1000) or =XGCHECK(A2:A10001,,B1) (where b1 holds an invoice number in either format).

To check after a specific invoice up to the max, =XGCHECK(A2:A10001,B1)

Hopefully that's enough examples for you to see all possible formats.

It's not instant (unless the first missing invoice is close to the start invoice number), but seems far quicker than the array formula.

Wow, I really appreciate the work you've put into this. I'll definitely check this out.