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

Question

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

Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! — TheMaster, Oct 05 '22 at 15:28

TheMaster · Answer 1 · 2022-10-05T20:12:41.173

1

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
)

edited Oct 05 '22 at 20:12

answered Oct 05 '22 at 15:40

TheMaster

45,448
6
62
85

player0 · Answer 2 · 2022-10-05T21:52:06.180

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)''")

Osm · Answer 3 · 2022-10-05T23:57:59.563

0

To aviod Lambda Limitations

See What factors determine the memory used in lambda functions?

=ArrayFormula({
 "Combination","Occurence";
  UNIQUE(A2:A&", "&B2:B),COUNTIF(A2:A&", "&B2:B, "="&UNIQUE(A2:A&", "&B2:B))})

↓↓↓↓↓ In this example we have 13,586 rows

For lambda function and LHFs

=ArrayFormula(LAMBDA(rg, 
 {"Combination","Occurence";
   UNIQUE( rg),COUNTIF(rg, "="&UNIQUE( rg))})
             (BYROW(A2:B23, LAMBDA(x, TEXTJOIN(", ",1,TRANSPOSE(SORT(TRANSPOSE(x),1,0)))))))

edited Oct 05 '22 at 23:57

answered Oct 05 '22 at 20:49

Osm

2,699
2
4
26

``Banana, Apple`` and ``Apple, Banana`` should count as the same entry. See question – TheMaster Oct 05 '22 at 23:02
Even without the hidden charachter the formula still amazing No arrayformula needed, sorting alphabetically byrow without `byrow` function – Osm Oct 05 '22 at 23:05
1

If you mean player0's non-lambda part of the answer, I can critique it: It's not scalable. What if there are 3 items per row? What if there are 30 items per row? `LAMBDA`, despite the limits, is much easier. – TheMaster Oct 05 '22 at 23:10

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

3 Answers3

To aviod Lambda Limitations

For lambda function and LHFs

Linked