Find most common combinations and how many times they occur

user_8303161 0
Viewed 85

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

enter image description here

google-sheets excel
5 Answers

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

enter image description here

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

enter image description here

I hope that helped ^_^

user_195296940

Alternate Solution (with Helper Columns):

Though the other solution posted works I've figured this will not count it in the same combination if the items are interchanged. For example:

enter image description here

This will be counted as 1 for each even they are the same combination.

So here's another solution if you don't mind using helper columns:

1.) Use this formula in 1 column to combine all items in the order:

=TEXTJOIN(", ", TRUE, SORT(TRANSPOSE(E2:I2), 1, TRUE))

Drag down to column.

enter image description here

This uses SORT() function to first sort the items alphabetically before using TEXTJOIN() function to concatenate the items into one cell. This is so that it will not matter even if the items are interchanged.

2.) Use the UNIQUE() function to remove the duplicates.

=UNIQUE(K2:K15)

enter image description here

3.) Use the COUNTIF() to count the number of occurences. Then the IF() to only apply it for rows that are not blank. Then ArrayFormula() so there's no need to drag down the formula to the column you just need to input in the first row.

=ARRAYFORMULA(IF(L2:L<>"",COUNTIF(K2:K,L2:L),""))

Final Result:

enter image description here

Limitation: This can't count as same combination if the total order is not the same. For example:

enter image description here

They will be counted as 1 each.

References:

user_184002720

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)), "♥")))

enter image description here

user_56326290

Solution with PowerQuery

You can add as many Item-Colums you want (Columnname must have the word "Item" in it -> "Item 6", "Item 7", "Last Item", "My Item", "Special Item" ...)

You do not have to adjust any range in a cell formula

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Inserted Merged Column" = Table.AddColumn(
    Source,
    "Combination",
        each Text.Combine( 
           List.Sort(
                List.Transform(
                    List.Select(
                        Table.ColumnNames(Source),
                        each Text.Contains(_,"Item")
                    )
                    , (col)=> Record.Field(_, col)
                )
            ), 
            "; "), type text
        ),
#"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"Combination"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}})
in
#"Sorted Rows"

enter image description here

user_134968800

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 enter image description here

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.

user_198421890
Related