Chapter 17 · 17.17
All of a customer's products in one cell, separated by commas
An order list has one row per product. For a packing slip, a mailing or an overview, you often want one row per customer instead: "Hotel De Haven: Krentenbollen, Croissants, Tompoucen". This used to need a macro. With TEXTJOIN and FILTER, a single formula does it.
As an example, we'll use the Bestellingen worksheet from Bakkerij De Korenaar again: the customer is in column B and the product in column C, from row 2 to row 13.
Please note: on Excel websites you'll also see a version with IF instead of FILTER: =TEXTJOIN(", ",1,IF(Bestellingen!B2:B13=C2,Bestellingen!C2:C13,"")). In Euro-Office, a plain Enter gives you an empty cell. It does work with Ctrl+Shift+Enter, but duplicate products stay in the list. FILTER with UNIQUE gives a tidier result.
Step 1: each customer once
Create an overview on another worksheet, for example with the customers in column C. In C2, type =SORT(UNIQUE(Bestellingen!B2:B13)). You'll get the customers listed alphabetically, one below the other (see Lists that update themselves with FILTER, UNIQUE and SORT).
Step 2: the products for each customer
Select E2 down to the row of the last customer (here E2:E5), type:
=TEXTJOIN(", ",1,UNIQUE(FILTER(Bestellingen!$C$2:$C$13,Bestellingen!$B$2:$B$13=C2)))
and press Ctrl+Enter so the formula appears next to every customer.
- FILTER fetches all products of the customer in C2.
- UNIQUE makes sure a product the customer ordered more than once is only listed once. If you do want to see repeats, leave out UNIQUE.
- TEXTJOIN joins them together. The first argument is the separator (a comma and a space), the second says whether empty cells should be skipped: 1 means yes.
For Hotel De Haven, the cell then shows "Krentenbollen, Croissants, Tompoucen".

Please note: type 1 or TRUE as the second argument. Euro-Office doesn't recognise the Dutch WAAR you may come across in Dutch Excel examples and returns #NAME? (see Copying formulas from Excel: English function names and the comma). TRUE() with brackets also works.
Variations
- Each product on its own line within the cell: use CHAR(10) as the separator, =TEXTJOIN(CHAR(10),TRUE,...), and turn on Wrap text on the Home tab. Handy for a packing slip.
- With quantities, such as "2x Appeltaart": add a helper column to the order list with =F2&"x "&C2 (with the quantity in F) and combine that column.
Pitfalls
- If a customer has a lot of products, a single cell becomes hard to read. In that case, list them one below the other with FILTER (see Lists that update themselves with FILTER, UNIQUE and SORT).
- A customer without products makes FILTER return an error value. Catch it with IFERROR(...,"").
- A colleague with Excel 2016 or older will see #NAME?. Convert the results to values first if you send the file on.
