Count the number of occurrences of a string combination with 2 columns-CodePudding

Hello I would like to count the number of occurrences of a string combination with 2 columns. For example :

Fruit A	Fruit B
Banana	Apple
Pear	Apricot
Apple	Banana
Apple	Pear
Banana	Apple

This should send me back:

Combination	Occurence
Banana, Apple	3
Pear, Apricot	1
Apple, Pear	1

if this is difficult we can imagine that fruit are in a single column like that:

Fruits
Banana, Apple
Pear, Apricot
Apple, Banana
Apple, Pear
Banana, Apple

Do you have some ideas how i can do ?

Thanks

CodePudding user response：

SORT each row using BYROW. This sorting makes both Apple,Banana and Banana,Apple become Apple,Banana. Once you have the array of same order items, use QUERY to group by and COUNT each combination:

=QUERY(
  BYROW(
    A1:INDEX(B:B,COUNTA(B:B)),
    LAMBDA(row,JOIN(",",SORT(TRANSPOSE(row))))
  ),
  "Select Col1,count(Col1) group by Col1",
  1
)

CodePudding user response：

To aviod Lambda Limitations

See

For a lambda function and LHS

=ArrayFormula(LAMBDA(rg, 
 {"Combination","Occurence";
   UNIQUE( rg),COUNTIF(rg, "="&UNIQUE( rg))})(BYROW(A2:B, LAMBDA(r, TEXTJOIN(", ",1,r)))))

CodePudding user response：

try:

=QUERY(FILTER(IF(A:A<B:B, A:A&", "&B:B, B:B&", "&A:A), LEN(A:A&B:B)), 
 "select Col1,count(Col1) group by Col1 label count(Col1)''")

or:

=QUERY(MAP(A1:A10, B1:B10, LAMBDA(x, y, IF(x>y, x&", "&y, y&", "&x))), 
 "select Col1,count(Col1) where Col1 <>', ' group by Col1 label count(Col1)''")

or:

=QUERY(LAMBDA(a, b, MAP(a, b, LAMBDA(x, y, IF(x<y, x&", "&y, y&", "&x))))
 (FILTER(A:A, A:A<>""), FILTER(B:B, A:A<>"")), 
 "select Col1,count(Col1) group by Col1 label count(Col1)''")

or if you want it separated:

=QUERY(FILTER(SPLIT(IF(A:A<B:B, A:A&""&B:B, B:B&""&A:A), ""), A:A&B:B<>""), 
 "select Col1,Col2,count(Col1) group by Col1,Col2 label count(Col1)''")