CODEWITHSUNDEEP
google-sheetsgoogle-sheets-query
Takeshi Patterson
Takeshi Patterson

Reputation: 1277

How to write a query that outputs the most common combinations with google query

I have a situation where I have a list of transaction IDs containing multiple product combinations. I'm trying to write a query that will show me the most common product combinations. In this sample data, I have the transaction ID in A, which links the products together, and the product name in B. What I want is an output that gives me the information in D and E - it doesn't have to look like that but needs to give the same information.

I would love to share my current workings but I have absolutely no idea how to even begin tackling this one, so any ideas would be warmly received.

https://docs.google.com/spreadsheets/d/1flJ4yM-E8v7A2nRMjjR1J27oqVa-eE5-8wH7TFd6UQg/edit#gid=1452792924

enter image description here

Upvotes: 1

Views: 69

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18707

Please try this formula:

=QUERY( {UNIQUE(FILTER(A4:A,A4:A>0)),TRANSPOSE(SPLIT(JOIN("",FILTER(B4:B&", "&if(A4:A<>OFFSET(A4:A,1,0),"|",""),A4:A<>0)),", |",0))}, "select Col2, count(Col1) group by Col2 order by count(Col1) desc label count(Col1) ''")

The formula assumes list of Ids is sorted. If not, then I suggest sort the data first.

Upvotes: 0

Ed Nelson
Ed Nelson

Reputation: 10259

Try this. In C4 put:

=unique(A4:A25)

In D4 put and copy down:

=join(", ",query(FILTER(A4:B25,A4:A25=C4),"Select Col2"))

In E4 put and copy down:

=JOIN(", ",SORT(TRANSPOSE(SPLIT(D4,", ",FALSE))))

In F4 put:

=QUERY({E:E,E:E},"select Col1, count(Col2) where Col1 != '' group by Col1 order by count(Col2) desc  label count(Col2) ''",1)

You can hide Col C, D, & E.

Upvotes: 2

Related Questions