Combination

Gap test on invoice numbers with FILTER, SEQUENCE and XMATCH

Find the missing numbers in a series of sales invoices: SEQUENCE builds every number from the lowest to the highest, XMATCH looks each one up in the ledger, ISNA marks the misses and FILTER lists them, with a message when there are no gaps.

Functions used:FILTERSEQUENCEXMATCHISNAExcel 2021 or later

Difficulty rating

Worked example

Missing numbers in the sales invoice series

Scenario

For the completeness of revenue at Veldhuis Installatie BV you have the invoice numbers of the sales journal (VRK) from the XAF auditfile, in the order they were posted.

Sales invoices are numbered in one continuous series, so a missing number can mean an invoice that was posted nowhere, deleted or cancelled without a trace. You want the list of missing numbers, to ask the client for an explanation and the underlying documents.

The data

ABCD
1Invoice no. (VRK)
225401254012540425404=FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)), "No gaps")=FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)); "No gaps")
325402254022540725407
425403254032540825408
52540525405
62540625406
72540925409
82540925409
92541025410
10
Formula cell (C2)Spilled result

Formula in C2

=FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)), "No gaps")
=FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)); "No gaps")

Result: 25404 / 25407 / 2540825404 / 25407 / 25408

How the pieces fit together

Watch out: The series runs from the lowest to the highest number in the data, so a missing first or last invoice of the period cannot be seen. Compare the lowest and highest number with the first and last number of the period according to the client, or type the expected start and end instead of MIN and MAX.

The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.

  1. =MAX(A2:A9)-MIN(A2:A9)+1
    =MAX(A2:A9)-MIN(A2:A9)+1

    The length of the series the numbers should form: the highest number minus the lowest, plus 1 because both ends count. 25410 - 25401 + 1 = 10 numbers.

  2. =SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9))
    =SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9))

    SEQUENCE generates the complete series in one column: 10 rows, one column, starting at the lowest number. The result is 25401, 25402, 25403, up to 25410. This is the list of numbers that should exist.

  3. =XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)
    =XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)

    XMATCH looks up each of the ten numbers in the ledger list. The result is one column: 1, 2, 3, #N/A, 4, 5, #N/A, #N/A, 6, 8. The #N/A values are 25404, 25407 and 25408. Number 25409 is found at its first position, 6, although it appears twice.

  4. =ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9))
    =ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9))

    ISNA turns every #N/A into TRUE and every position into FALSE: FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE. This is the include list for FILTER, one answer for each number of the series.

  5. =FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)), "No gaps")
    =FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)); "No gaps")

    FILTER keeps the numbers of the series where the include list is TRUE: positions 4, 7 and 8, so 25404, 25407 and 25408. The third argument is the text to show when nothing qualifies; here three numbers qualify, so you see the three gaps.

Step by step

  1. MAX(A2:A9) is 25410 and MIN(A2:A9) is 25401, so the series has 25410 - 25401 + 1 = 10 numbers.
  2. SEQUENCE(10, 1, 25401) builds the column 25401, 25402, 25403, 25404, 25405, 25406, 25407, 25408, 25409, 25410.
  3. XMATCH looks up those ten numbers in A2:A9: 25401 at position 1, 25402 at 2, 25403 at 3, 25404 not found, 25405 at 4, 25406 at 5, 25407 not found, 25408 not found, 25409 at 6 (first hit), 25410 at 8.
  4. ISNA turns that into FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE: TRUE for 25404, 25407 and 25408 only.
  5. FILTER keeps those three numbers from the series. Three qualify, so the message is not used. The result spills over C2:C4: 25404, 25407, 25408.
  6. Check by counting: 8 rows, of which 25409 is a duplicate, gives 7 different numbers. The series has 10, and 10 - 7 = 3 gaps, which matches the three numbers found.

Why it works

The ledger can only show you the numbers that were posted, so the test starts from the numbers that should exist. Each function has one job:

  • SEQUENCE builds that list from the lowest and highest number in the data
  • XMATCH returns a position for a number that is posted and #N/A for one that is not
  • ISNA turns exactly that difference into TRUE and FALSE
  • FILTER needs one answer per number of the series, and XMATCH gives one for each

The third argument covers the clean case with no gaps.

The formula needs Excel for Microsoft 365 or Excel 2021 or later, because FILTER, SEQUENCE and XMATCH do not exist in earlier versions.

Watch out for

  • Two data problems distort the result:

    • a mistyped number (25490 instead of 25409) stretches the series and floods the result with false gaps
    • a blank cell inside the range counts as one extra unique value (a 0), so the gap count with ROWS(UNIQUE(A2:A9)) comes out one too low

    Clean both before you test.

  • The numbers must be real numbers. A number such as 25-0412 or an invoice number stored as text does not work with MIN, MAX and SEQUENCE. Strip the prefix and convert the rest to a number first.

    Also test each series on its own: credit notes or a second administration usually have their own numbering.

  • A gap is a question for the client, not yet a misstatement. The number may be a cancelled invoice, a test invoice or a number skipped by the system. Ask for the explanation and the cancelled document.

    A duplicate number, like 25409 here, also needs a question: it can be two postings of one invoice or a correction.

Variations

=FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), COUNTIF(A2:A9, SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)))=0, "No gaps")
=FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); COUNTIF(A2:A9; SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)))=0; "No gaps")

The older approach: COUNTIF counts how often each number of the series occurs in the ledger list and FILTER keeps the zeros. Same three numbers, but slower on long lists, and it still needs SEQUENCE and FILTER.

=MAX(A2:A9)-MIN(A2:A9)+1-ROWS(UNIQUE(A2:A9))
=MAX(A2:A9)-MIN(A2:A9)+1-ROWS(UNIQUE(A2:A9))

The number of gaps without listing them: the length of the series minus the number of different numbers. 10 - 7 = 3. Use it as a control total next to the list. It assumes whole numbers only.

=FILTER(UNIQUE(A2:A9), COUNTIF(A2:A9, UNIQUE(A2:A9))>1, "No duplicates")
=FILTER(UNIQUE(A2:A9); COUNTIF(A2:A9; UNIQUE(A2:A9))>1; "No duplicates")

The duplicates check: UNIQUE gives the seven different numbers, COUNTIF counts each in A2:A9 (1, 1, 1, 1, 1, 2, 1) and FILTER keeps those counted more than once. The result is 25409.

All combinations