Trying to find the most popular combinations as per the spreadsheet. The desired output should list all the unique ordered combinations, eg Prod A and Prod B is equivalent to Prod B and Prod A. Looking for an Excel 2019 solution with formulae and ok to have helper columns.
Book1 | ||||
---|---|---|---|---|
B | C | |||
1 | Customer | Sales | ||
2 | Fred | Prod A, Prod C, Prod B | ||
3 | Harry | Prod C, Prod A | ||
4 | Ken | Prod C, Prod A | ||
5 | Vic | Prod A, Prod C | ||
6 | Nellie | Prod B, Prod A, Prod C | ||
7 | Sally | Prod A | ||
8 | Joe | Prod B, Prod A | ||
9 | Ted | Prod B, Prod C, Prod A | ||
10 | ||||
11 | Desired output | Count from largest to least | ||
12 | Prod A, Prod B, Prod C | 3 | ||
13 | Prod A, Prod C | 3 | ||
14 | Prod A, Prod B | 1 | ||
15 | etc | |||
Sheet11 |