Find most common combinations and how many times they occur-CodePudding

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

CodePudding user response：

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))) })

I hope that helped ^_^

CodePudding user response：

It's a tough one with multiple combinations and order sequence matters. A not so complete answer for only the first two items would be:

Formulas Layout

In Cell K2 =E2&" "&F2 In Cell M2 =COUNTIF($E:$I,L2) In Cell O2 =COUNTIFS($K:$K,$L2&" "&O$1)

That would only add up the first two items in each order in a matrix style layout and I added conditional formatting for viewing higher numbers in the matrix.