Combination
All invoices of one supplier in one cell with TEXTJOIN and FILTER
Pick the invoice numbers of one supplier with FILTER and join them into a single line with TEXTJOIN, with a message when the supplier has no invoices.
Functions used:TEXTJOINFILTERExcel 2021 or later (This combination needs Excel 2021 or later)
Worked example
Invoice numbers for a supplier confirmation letter
Scenario
For the year-end audit of Veldhuis Installatie BV you send confirmation letters to suppliers. The letter for supplier C2001 must list every invoice of that supplier in this extract, in one line of the letter text.
You have the purchase journal (INK) lines for November and December 2025, one per invoice, and you want the invoice numbers in a single cell instead of copying them by hand.
The data
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Relation | Invoice no. | Date | Amount | Supplier | C2001 | |||
| 2 | C2001 | 25-0412 | 3 Nov 2025 | €4,210.50€ 4.210,50 | Invoices | 25-0412, 25-0431, 25-0457, 25-0471=TEXTJOIN(", ", TRUE, FILTER(B2:B9, A2:A9=H1, "No invoices"))=TEXTJOIN(", "; TRUE; FILTER(B2:B9; A2:A9=H1; "No invoices")) | |||
| 3 | C2002 | 25-0415 | 10 Nov 2025 | €1,890.00€ 1.890,00 | |||||
| 4 | C2001 | 25-0431 | 24 Nov 2025 | €12,600.00€ 12.600,00 | |||||
| 5 | C2003 | 25-0440 | 2 Dec 2025 | €760.25€ 760,25 | |||||
| 6 | C2001 | 25-0457 | 15 Dec 2025 | €3,350.00€ 3.350,00 | |||||
| 7 | C2002 | 25-0463 | 19 Dec 2025 | €2,480.00€ 2.480,00 | |||||
| 8 | C2001 | 25-0471 | 29 Dec 2025 | €905.75€ 905,75 | |||||
| 9 | C2003 | 25-0476 | 30 Dec 2025 | €5,120.00€ 5.120,00 | |||||
| 10 |
Swipe sideways to see all columns
Formula in H2
=TEXTJOIN(", ",TRUE, FILTER(B2:B9, A2:A9=H1, "No invoices"))
=TEXTJOIN(", ";TRUE; FILTER(B2:B9; A2:A9=H1; "No invoices"))
Result: 25-0412, 25-0431, 25-0457, 25-0471
How the pieces fit together
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=A2:A9=H1
=A2:A9=H1
ReferenceRelation column, 8 cellsPress Escape to close=Equal toPress Escape to closeReferenceInput cell: C2001Press Escape to closeCompares each relation code in
A2:A9with the supplier inH1(C2001). The result is a column of eight values: TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE. Four rows belong toC2001.=FILTER(B2:B9,
A2:A9=H1) =FILTER(B2:B9;
A2:A9=H1) FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.Invoice no. column, 8 cellsPress Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Relation column, 8 cellsPress Escape to close=Equal toPress Escape to closeReferenceInput cell: C2001Press Escape to closeFILTER keeps the invoice numbers where the comparison is TRUE:
25-0412,25-0431,25-0457and25-0471. That is a column of four cells, still spread over four rows.=FILTER(B2:B9,
A2:A9=H1, "No invoices") =FILTER(B2:B9;
A2:A9=H1; "No invoices") FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.Invoice no. column, 8 cellsPress Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Relation column, 8 cellsPress Escape to close=Equal toPress Escape to closeReferenceInput cell: C2001Press Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeThe third argument is returned when nothing matches. For
C2001it is not used and the four invoice numbers come back. For a code such asC2009, which is not in the list, you would see the text instead of the #CALC! error.=TEXTJOIN(", ",
TRUE, FILTER(B2:B9, A2:A9=H1, "No invoices")) =TEXTJOIN(", ";
TRUE; FILTER(B2:B9; A2:A9=H1; "No invoices")) TEXTJOIN TEKST.COMBINERENJoins texts with a separator, optionally skipping blanks.Read more about TEXTJOINPress Escape to closedelimiterThe text placed between the items, in quotes, such as ", ".Press Escape to closeignore_emptyTRUE skips empty cells; FALSE keeps them and doubles the separator.Logical valuePress Escape to closeFILTERReturns every row of a range that meets a condition.Used here as: text1The first text, cell, range or array to join.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.Invoice no. column, 8 cellsPress Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Relation column, 8 cellsPress Escape to close=Equal toPress Escape to closeReferenceInput cell: C2001Press Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeTEXTJOIN glues the four invoice numbers together with a comma and a space between them:
25-0412, 25-0431, 25-0457, 25-0471. If FILTER returns the message, TEXTJOIN returns that text.
Step by step
- A2:A9=H1 compares the eight relation codes with
C2001: TRUE at positions 1, 3, 5 and 7 of the list, FALSE in the other four. - FILTER(B2:B9, ...) keeps the invoice numbers at those positions:
25-0412,25-0431,25-0457,25-0471. Four of eight rows remain. - Because there are matches, the third argument ("No invoices") is not used.
- TEXTJOIN(", ", TRUE, ...) puts ", " between the four values:
25-0412, 25-0431, 25-0457, 25-0471. - Check: 4 invoice numbers of 7 characters plus 3 separators of 2 characters is 28 + 6 = 34 characters in cell
H2.
Why it works
FILTER does the selecting and returns the matches as a list; TEXTJOIN takes a whole list as one argument and turns it into text. Because FILTER sits inside TEXTJOIN, you never need helper cells.
The third argument of FILTER also covers the empty case, so the letter never shows an error for a supplier without invoices.
Watch out for
- If the supplier code in
H1has a trailing space ("C2001 " copied from a mail), nothing matches and you see the message. Clean the input with TRIM or retype it. - The result is one piece of text. A cell holds at most 32,76732.767 characters, so when the joined result would exceed that, TEXTJOIN returns #VALUE! instead of a shortened text. For letters this is rarely a problem, but check the count for large suppliers.
- The order follows the data, not the invoice number. If the purchase journal is not sorted by invoice number, the line in the letter is not either. Wrap FILTER in SORT when the order matters.
Variations
=TEXTJOIN(", ",TRUE, IF(A2:A9=H1, B2:B9, ""))
=TEXTJOIN(", ";TRUE; IF(A2:A9=H1; B2:B9; ""))
The older construction without FILTER: IF returns the invoice number for the matching rows and an empty text for the others, and TRUE tells TEXTJOIN to skip the empty ones. Same result, but no message for a supplier without invoices (the cell is empty), and in versions before Excel 365 it must be entered with Ctrl+Shift+Enter.
=TEXTJOIN(", ",TRUE, SORT(FILTER(B2:B9, A2:A9=H1, "No invoices")))
=TEXTJOIN(", ";TRUE; SORT(FILTER(B2:B9; A2:A9=H1; "No invoices")))
SORT puts the invoice numbers in ascending order before they are joined. The data is already in that order, so the result is the same here; it makes the letter safe when the export is sorted by date or by amount.
Related combinations
Functions used