Find most common combinations and how many times they occur

Question

In the example below I want to search each unique order and then the items in that order. From that I would like to extract the most common items that are ordered together and how many times they occur together. This is just a sample. I am doing this with a file with 20,000 rows.

Sorry, I haven't earned enough points to embed the photo. It's in the link below.

Screenshot of the example

Answer 1

Use this formula to get the occurrences with one formula one cell.

=ArrayFormula({ "Occurrences",$B$1:$F$1; 
     QUERY({COUNTIF(
 B2:B&C2:C&D2:D&E2:E&F2:F,
 "="&QUERY({ARRAY_CONSTRAIN(UNIQUE($B$2:$F),ROWS(UNIQUE($B$2:$F))-1,COLUMNS(UNIQUE($B$2:$F)))}, " Select  Col1 ")&
     QUERY({ARRAY_CONSTRAIN(UNIQUE($B$2:$F),ROWS(UNIQUE($B$2:$F))-1,COLUMNS(UNIQUE($B$2:$F)))}, " Select  Col2 ")&
     QUERY({ARRAY_CONSTRAIN(UNIQUE($B$2:$F),ROWS(UNIQUE($B$2:$F))-1,COLUMNS(UNIQUE($B$2:$F)))}, " Select  Col3 ")&
     QUERY({ARRAY_CONSTRAIN(UNIQUE($B$2:$F),ROWS(UNIQUE($B$2:$F))-1,COLUMNS(UNIQUE($B$2:$F)))}, " Select  Col4 ")&
     QUERY({ARRAY_CONSTRAIN(UNIQUE($B$2:$F),ROWS(UNIQUE($B$2:$F))-1,COLUMNS(UNIQUE($B$2:$F)))}, " Select  Col5 "))
           }, "Select Col1 where Col1 <> 0 "),
            ARRAY_CONSTRAIN(UNIQUE($B$2:$F),ROWS(UNIQUE($B$2:$F))-1,COLUMNS(UNIQUE($B$2:$F))) })

Option 02

=ArrayFormula({ "Occurrences",$B$1:$F$1; 
     QUERY({ARRAY_CONSTRAIN(COUNTIF(
            FLATTEN(QUERY(TRANSPOSE(B2:F), "",9^9 )),
        "="&FLATTEN(QUERY(TRANSPOSE(ARRAY_CONSTRAIN(UNIQUE($B$2:$F),ROWS(UNIQUE($B$2:$F))-1,COLUMNS(UNIQUE($B$2:$F)))), "",9^9 ))),
     COUNTA(FLATTEN(QUERY(TRANSPOSE(ARRAY_CONSTRAIN(UNIQUE($B$2:$F),ROWS(UNIQUE($B$2:$F))-1,COLUMNS(UNIQUE($B$2:$F)))), "",9^9 ))),1)
           }, "Select Col1 where Col1 <> 0 "),
            ARRAY_CONSTRAIN(UNIQUE($B$2:$F),ROWS(UNIQUE($B$2:$F))-1,COLUMNS(UNIQUE($B$2:$F))) })

I hope that helped ^_^

Answer 2

try this and notice the blue cells:

=ARRAYFORMULA(TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(QUERY(TRIM(SPLIT(FLATTEN(
 QUERY(QUERY(IFERROR(SPLIT(FLATTEN(IF(E2:I="",,ROW(E2:I)&"♠♦"&PROPER(E2:I)&"♥")), "♦")), 
 "select max(Col2) where Col1 <> '♠' group by Col2 pivot Col1"),,9^9)), "♠")), 
 "select count(Col2),Col2 where Col2 is not null group by Col2 order by count(Col2) desc"), 
 "select Col1,'♥',Col2"), "offset 1", )),,9^9)), "♥")))