Combination
Duplicate invoice numbers across two exports
Stack two monthly purchase exports into one list and keep each invoice number that occurs more than once, using VSTACK, UNIQUE, XMATCH, ISNA and FILTER.
Functions used:VSTACKUNIQUEFILTERXMATCHISNAExcel 2024 or later (This combination needs Excel 2024 or later)
Difficulty rating 4 of 5
Worked example
Invoice numbers that occur more than once across the January and February exports
Scenario
While testing the purchase cycle of Veldhuis Installatie BV you received the purchase invoice numbers of January and February as two separate exports. You want one list of every invoice number that occurs more than once across both months, so you can check whether an invoice was booked twice.
The data
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | January | February | |||
| 2 | 25-0412 | 25-0420 | 25-0420=FILTER(VSTACK(A2:A7, B2:B7), ISNA(XMATCH(VSTACK(A2:A7, B2:B7), UNIQUE(VSTACK(A2:A7, B2:B7), , TRUE))), "No duplicates")=FILTER(VSTACK(A2:A7; B2:B7); ISNA(XMATCH(VSTACK(A2:A7; B2:B7); UNIQUE(VSTACK(A2:A7; B2:B7); ; TRUE))); "No duplicates") | ||
| 3 | 25-0415 | 25-0446 | 25-0433 | ||
| 4 | 25-0420 | 25-0433 | 25-0420 | ||
| 5 | 25-0433 | 25-0452 | 25-0433 | ||
| 6 | 25-0437 | 25-0420 | 25-0420 | ||
| 7 | 25-0441 | 25-0458 | |||
| 8 |
Swipe sideways to see all columns
Formula in D2
=FILTER(VSTACK(A2:A7,B2:B7), ISNA(XMATCH(VSTACK(A2:A7, B2:B7), UNIQUE(VSTACK(A2:A7, B2:B7), , TRUE))), "No duplicates")
=FILTER(VSTACK(A2:A7;B2:B7); ISNA(XMATCH(VSTACK(A2:A7; B2:B7); UNIQUE(VSTACK(A2:A7; B2:B7); ; TRUE))); "No duplicates")
FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to close
VSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: arrayThe rows you want back: one column or a whole table.Read more about VSTACKPress Escape to close
array1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to close
array2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to close
ISNA ISNBTRUE when a value is #N/A.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Press Escape to close
XMATCH X.VERGELIJKENReturns the position of a value in a range.Used here as: valueThe value or cell to test.Read more about XMATCHPress Escape to close
VSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: lookup_valueThe value whose position you want.Read more about VSTACKPress Escape to close
array1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to close
array2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to close
UNIQUE UNIEKReturns the distinct values of a range.Used here as: lookup_arrayThe single row or column to search. Positions count from its first cell.Read more about UNIQUEPress Escape to close
VSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: arrayThe values to deduplicate; with several columns, whole rows are compared.Read more about VSTACKPress Escape to close
array1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to close
array2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to close
exactly_once (optional)TRUE keeps only values that occur once; FALSE or omitted keeps one of each.Logical valuePress Escape to close
if_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to close
Result: 25-0420 / 25-0433 / 25-0420 / 25-0433 / 25-042025-0420 / 25-0433 / 25-0420 / 25-0433 / 25-0420
How the pieces fit together
Watch out: Invoice numbers that differ only by a trailing space, or by text versus number type, count as different, so a real duplicate can slip through. Clean the exports with TRIM first when they come from different systems.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=VSTACK(A2:A7,
B2:B7) =VSTACK(A2:A7;
B2:B7) array1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to closearray2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to closePuts the January export on top of the February export: one column of 12 invoice numbers, from
25-0412down to25-0458. Nothing is removed yet;25-0420appears three times and25-0433twice.=UNIQUE(VSTACK(A2:A7,
B2:B7), , TRUE) =UNIQUE(VSTACK(A2:A7;
B2:B7); ; TRUE) VSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: arrayThe values to deduplicate; with several columns, whole rows are compared.Read more about VSTACKPress Escape to closearray1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to closearray2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to closeexactly_once (optional)TRUE keeps only values that occur once; FALSE or omitted keeps one of each.Logical valuePress Escape to closeWith the third argument TRUE, UNIQUE returns only the values that occur exactly once.
Of the 12 numbers,
25-0420(3 times) and25-0433(2 times) drop out, which leaves 7:25-0412,25-0415,25-0437,25-0441,25-0446,25-0452and25-0458.=XMATCH(VSTACK(A2:A7,
B2:B7), UNIQUE(VSTACK(A2:A7, B2:B7), , TRUE)) =XMATCH(VSTACK(A2:A7;
B2:B7); UNIQUE(VSTACK(A2:A7; B2:B7); ; TRUE)) XMATCH X.VERGELIJKENReturns the position of a value in a range.Read more about XMATCHPress Escape to closeVSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: lookup_valueThe value whose position you want.Read more about VSTACKPress Escape to closearray1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to closearray2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: lookup_arrayThe single row or column to search. Positions count from its first cell.Read more about UNIQUEPress Escape to closeVSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: arrayThe values to deduplicate; with several columns, whole rows are compared.Read more about VSTACKPress Escape to closearray1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to closearray2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to closeexactly_once (optional)TRUE keeps only values that occur once; FALSE or omitted keeps one of each.Logical valuePress Escape to closeLooks up each of the 12 stacked numbers in the list of 7. A number that occurs once gets its position (1, 2, 3, ...); a repeated number is not in the list and gives #N/A. The result is 1, 2, #N/A, #N/A, 3, 4, #N/A, 5, #N/A, 6, #N/A, 7.
=ISNA(XMATCH(VSTACK(A2:A7,
B2:B7), UNIQUE(VSTACK(A2:A7, B2:B7), , TRUE))) =ISNA(XMATCH(VSTACK(A2:A7;
B2:B7); UNIQUE(VSTACK(A2:A7; B2:B7); ; TRUE))) ISNA ISNBTRUE when a value is #N/A.Press Escape to closeXMATCH X.VERGELIJKENReturns the position of a value in a range.Used here as: valueThe value or cell to test.Read more about XMATCHPress Escape to closeVSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: lookup_valueThe value whose position you want.Read more about VSTACKPress Escape to closearray1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to closearray2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: lookup_arrayThe single row or column to search. Positions count from its first cell.Read more about UNIQUEPress Escape to closeVSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: arrayThe values to deduplicate; with several columns, whole rows are compared.Read more about VSTACKPress Escape to closearray1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to closearray2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to closeexactly_once (optional)TRUE keeps only values that occur once; FALSE or omitted keeps one of each.Logical valuePress Escape to closeTurns every #N/A into TRUE and every position into FALSE: FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE.
The TRUE values sit at positions 3, 4, 7, 9 and 11, the repeated invoices.
=FILTER(VSTACK(A2:A7,
B2:B7), ISNA(XMATCH(VSTACK(A2:A7, B2:B7), UNIQUE(VSTACK(A2:A7, B2:B7), , TRUE))), "No duplicates") =FILTER(VSTACK(A2:A7;
B2:B7); ISNA(XMATCH(VSTACK(A2:A7; B2:B7); UNIQUE(VSTACK(A2:A7; B2:B7); ; TRUE))); "No duplicates") FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to closeVSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: arrayThe rows you want back: one column or a whole table.Read more about VSTACKPress Escape to closearray1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to closearray2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to closeISNA ISNBTRUE when a value is #N/A.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Press Escape to closeXMATCH X.VERGELIJKENReturns the position of a value in a range.Used here as: valueThe value or cell to test.Read more about XMATCHPress Escape to closeVSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: lookup_valueThe value whose position you want.Read more about VSTACKPress Escape to closearray1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to closearray2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: lookup_arrayThe single row or column to search. Positions count from its first cell.Read more about UNIQUEPress Escape to closeVSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: arrayThe values to deduplicate; with several columns, whole rows are compared.Read more about VSTACKPress Escape to closearray1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to closearray2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to closeexactly_once (optional)TRUE keeps only values that occur once; FALSE or omitted keeps one of each.Logical valuePress Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeFILTER keeps the stacked numbers at the TRUE positions:
25-0420,25-0433,25-0420,25-0433,25-0420. That is 5 rows, one for every time a repeated invoice appears. If nothing were repeated, the cell would show the text for no duplicates.
Step by step
- VSTACK(A2:A7, B2:B7) puts February under January: 6 + 6 = 12 numbers. Positions 1 to 6 are
25-0412,25-0415,25-0420,25-0433,25-0437,25-0441; positions 7 to 12 are25-0420,25-0446,25-0433,25-0452,25-0420,25-0458. - Counting the occurrences:
25-0420appears at positions 3, 7 and 11 (3 times),25-0433at positions 4 and 9 (2 times), and the other 7 numbers once each. Check: 3 + 2 + 7 = 12. - UNIQUE(..., , TRUE) keeps only the numbers that occur exactly once:
25-0412,25-0415,25-0437,25-0441,25-0446,25-0452,25-0458. That is 7 numbers. - XMATCH looks up all 12 stacked numbers in those 7: 1, 2, #N/A, #N/A, 3, 4, #N/A, 5, #N/A, 6, #N/A, 7. The repeated numbers are not in the list, so they give #N/A.
- ISNA turns that into FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE. TRUE marks positions 3, 4, 7, 9 and 11.
- FILTER returns the stacked numbers at those positions:
25-0420,25-0433,25-0420,25-0433,25-0420. The list spills fromD2down toD6, 5 rows.
Why it works
UNIQUE with exactly_once TRUE gives the numbers that are fine, the ones that occur once. Everything in the stack that is not in that list must occur more than once, and XMATCH combined with ISNA turns that question into TRUE or FALSE per row. FILTER then keeps the TRUE rows.
Because VSTACK produces an array and not a range, every step works on the whole stack at once, with no helper column.
Watch out for
- COUNTIF cannot do this on the stacked list. COUNTIF needs a range as its first argument, and VSTACK returns an array, so =COUNTIF(VSTACK(A2:A7, B2:B7), A2) gives an error. That is why the construction uses UNIQUE and XMATCH, which accept arrays.
- The result lists every occurrence of a repeated invoice, not every invoice once:
25-0420appears three times because it was exported three times. Wrap the formula in UNIQUE if you want one line per invoice number.
Variations
=UNIQUE(FILTER(VSTACK(A2:A7,B2:B7), ISNA(XMATCH(VSTACK(A2:A7, B2:B7), UNIQUE(VSTACK(A2:A7, B2:B7), , TRUE))), "No duplicates"))
=UNIQUE(FILTER(VSTACK(A2:A7;B2:B7); ISNA(XMATCH(VSTACK(A2:A7; B2:B7); UNIQUE(VSTACK(A2:A7; B2:B7); ; TRUE))); "No duplicates"))
FILTERReturns every row of a range that meets a condition.Used here as: arrayThe values to deduplicate; with several columns, whole rows are compared.Read more about FILTERPress Escape to close
VSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: arrayThe rows you want back: one column or a whole table.Read more about VSTACKPress Escape to close
array1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to close
array2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to close
ISNA ISNBTRUE when a value is #N/A.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Press Escape to close
XMATCH X.VERGELIJKENReturns the position of a value in a range.Used here as: valueThe value or cell to test.Read more about XMATCHPress Escape to close
VSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: lookup_valueThe value whose position you want.Read more about VSTACKPress Escape to close
array1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to close
array2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to close
UNIQUE UNIEKReturns the distinct values of a range.Used here as: lookup_arrayThe single row or column to search. Positions count from its first cell.Read more about UNIQUEPress Escape to close
VSTACK VERT.STAPELENStacks ranges on top of each other.Used here as: arrayThe values to deduplicate; with several columns, whole rows are compared.Read more about VSTACKPress Escape to close
array1The first range or array: it ends up at the top of the result.January column, 6 cellsPress Escape to close
array2 (optional)Further ranges or arrays, each placed below the previous one.February column, 6 cellsPress Escape to close
exactly_once (optional)TRUE keeps only values that occur once; FALSE or omitted keeps one of each.Logical valuePress Escape to close
if_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to close
UNIQUE around the whole formula: one line per repeated invoice, so 25-0420 and 25-0433 (2 rows instead of 5).
=FILTER(A2:A7,COUNTIF(A2:B7, A2:A7) > 1, "No duplicates")
=FILTER(A2:A7;COUNTIF(A2:B7; A2:A7) > 1; "No duplicates")
FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to close
arrayThe rows you want back: one column or a whole table.January column, 6 cellsPress Escape to close
COUNTIF AANTAL.ALSCounts cells that meet one condition.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Read more about COUNTIFPress Escape to close
rangeThe cells to test.6 rows by 2 columnsPress Escape to close
criteriaThe condition a cell must meet to be counted.January column, 6 cellsPress Escape to close
>Greater thanPress Escape to close
NumberLiteral valuePress Escape to close
if_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to close
The older approach with COUNTIF on the real range A2:B7, without VSTACK. It counts each January number in both columns (25-0420 gives 3, 25-0433 gives 2, the rest 1) and returns 25-0420 and 25-0433. It only lists the January side, and it needs the data in one block of cells.
Related combinations
Functions used
- VSTACKPuts ranges or arrays below each other into one spilled block, so separate exports become a single list you can sort, filter or deduplicate.
- UNIQUEReturns each value of a range once, in the order it first appears, as a list that spills and follows the source.
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- XMATCHFinds a value in a row or column and returns its position as a number, with options for nearest matches, wildcards, regular expressions and searching from the end.
Functions used
- VSTACKPuts ranges or arrays below each other into one spilled block, so separate exports become a single list you can sort, filter or deduplicate.
- UNIQUEReturns each value of a range once, in the order it first appears, as a list that spills and follows the source.
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- XMATCHFinds a value in a row or column and returns its position as a number, with options for nearest matches, wildcards, regular expressions and searching from the end.