Most frequent combinations

[tigerzen]

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

 

[ISY]

Hi

Cell D2
=BYROW(C2:C9,LAMBDA(k,TEXTJOIN("", "",,SORT(TEXTSPLIT(k,"", ""),,,1))))

Cell B12
=UNIQUE(D2#)

Cell C12
=COUNTIF(D2#,UNIQUE(D2#))

 

[tigerzen]

Hi

Cell D2
=BYROW(C2:C9,LAMBDA(k,TEXTJOIN("", "",,SORT(TEXTSPLIT(k,"", ""),,,1))))

Cell B12
=UNIQUE(D2#)

Cell C12
=COUNTIF(D2#,UNIQUE(D2#))

Thanks ISY, I'm playing around with your solution but I'm getting error messages in D2, ie VALUE errors. I 'll keep trying to see if I can get this to work.

 

[Fluff]

There are some extra quotes in the formula, try

=BYROW(C2:C9,LAMBDA(k,TEXTJOIN(", ",,SORT(TEXTSPLIT(k,", "),,,1))))

 

[tigerzen]

There are some extra quotes in the formula, try

=BYROW(C2:C9,LAMBDA(k,TEXTJOIN(", ",,SORT(TEXTSPLIT(k,", "),,,1))))

Thanks Fluff, that works perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.