Consolidated list of products

Imagine this scenario: you register customers in an Excel spreadsheet, specifying their ID and then the products they use. Which customer uses which products? One way to get this answer is to create a pivot table, and then display customers, products and the number of products. The disadvantage of this report is that pivot tables always aggregate data. What if we only need a table with customer ID in one column and a consolidated list of products in another?

I have to admit that the DAX function CONCATENATEX was the first thing that came to my mind as a solution. It is an iterator, and allows us to generate a consolidated list of products, line by line. Could it be simpler? Of course, with the use of functions for working with dynamic arrays!

First, let’s generate a customer list. We will do this by taking the unique values of customers from the first column of the table. To make the list look nicer, we will also sort it. The formula:

=SORT(UNIQUE(A2:A11))

Now we have criteria for generating a consolidated list of products. In order to fill out the list, it is necessary to filter the product values based on criteria – customer ID. Next, if we only want to get a concatenated string with product labels, we will use the filtered table as an argument of the CONCAT function. However, I think it is more elegant solution is to use the TEXTJOIN function. Formula:

=TEXTJOIN(“,”,,FILTER(\$B\$2:\$B\$11,\$A\$2:\$A\$11=D2))

The specified expression extracts each individual product used by the given customer (D2), and then concatenates it into the list, a string with product names se separated by commas.