Pivot table - how to group combinations of value

This proposal adds a column (C) to the source data which is populated using the following array entered formula**:

Formula:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

Thanks for your help. I am going to pick your brain again if that's okay, I hope you like a challenge.

In this example, one of the customers - customer 2345 - has bought the same product twice, but I only want it to show once in the final order column. Currently, it is showing it twice as 'Green, Red, Red', but I want it to show as 'Green, Red'.

Secondly, some of the customers have the same products but they purchased them at different times. Customers 3456 and 4567 have both bought red and white paint, however, because customer 3456 bought white first the final order column is showing as 'White, Red'. Customer 4567 bought red first and it is showing as 'Red, White'.

Can you think of a way around this so it is only showing each colour once in the final order column? Secondly, so the combinations in the final order column are always the same, even if customers purchased the products at different times to one another.

I've put this in the New Version tab of this spreadsheet, hopefully it makes sense.

Thanks again.

Not elegant, however it seems to work as modeled on the Old version sheet:
1. Sort the paint colors ordered column A to Z
2. Add a column (C) populated using: =IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,B2,"")
3. Modify the array entered formula** in the Final order columns to read: =IF(COUNTIFS(A$2:A2,A2)=1,TEXTJOIN(", ",1,IF(A$2:A$12=A2,C$2:C$12,"")),"")
**See post #2
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.