I'm not sure if this will be enough, but in the first pivot ( 'Not a complete solution '), you can remove the second dimension and add the following expression:
concat ( DISTINCT CustomerID, ', ')
The result will be the one you described in your post: customerID = A,C,E,F,K
Hope this helps,
Hi. Thanks for your suggestion.
However this is not what I want. My post was misleading ( I don't want a list like A,C,E in a row)
I want a list where customeID is the only (visible) column. (as descibed in the docuement)
The reason is that in me "real world" data, there are 10'000 customers and 100'000 invoices, and we can see that there are duplicates in invoice ID, which makes a merge not possible. So I want to list all customers that have invoice numbers that are duplicates (or actually I need a list of the opposite, customers which have no duplicate invoices.
Simply - a list all customersID's where there is a duplicate invoice. And only list a customer ID once (even if that custumer has several duplicate invoices).
Just noticed that if I create a chart with CustomerID as the only dimension and this expression
I end up getting a list of customerID's that has no duplicate invoices (which is kinda what I want). It seems to be the solution to my request, but I don't really understand why and how it works... and I still need the opposite list -> List of customers who have duplicate invoices with other customers... //Robert
Create a listbox for InvoiceID if you don't already have one. Click on the titlebar of that listbox to select it. Now, type this: =count(distinct CustomerID)>1 and hit <enter>. That will select all of the InvoiceID values belonging to more than one customer. At this point your chart may be the way you want it.
If you want to select those customers go to a listbox for the CustomerID, right-click, and choose Select Possible and it will have selected the Customer who share the same InvoiceID. At that point, you may unselect the InvoiceID if necessary.