Skip to content

Cookie settings

Choose which kinds of cookies you allow. You can change your choice at any time via Cookie settings at the bottom of every page. Privacy statement

Necessary

Always on

Needed to log in, keep your cart and remember your choices. These are always on.

Which cookies?
  • weboke_sessie, weboke_klant, weboke_remember: your session and staying logged in
  • weboke_basket: your cart
  • wkBtwIncl: showing prices with or without VAT
  • wok_cookies: your cookie choice, 12 months

Measures which ad leads to a visit or order, so we do not pay for ads that do nothing.

Which cookies?
  • Google Ads via Google Tag Manager (_gcl_au, _gcl_aw), up to 90 days
  • OpenAI Ads, ads in ChatGPT
  • wok_aff: which partner referred you to us (affiliate programme), 60 days

4.6

Country and language

Each country has its own site, with prices and VAT for that country.

View

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

Products per customer in one cell
Each customer's products in one cell, separated by commas. The customers are in column C (1), and the formula bar shows the formula in E2

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.

One moment…

Set up your domains

For each domain, arrange the nameservers, the link to your hosting and who becomes the owner.

Loading your domains…