PDA

View Full Version : Items frequently bought together



mike1984
10-01-2015, 03:52 AM
Hi all,

I'm looking for a formula which will tell me the unique number of transactions where two items have been purchased together.

I have searched the forum and found this thread which is part way to getting what I am after (last post), but it does not count unique transactions.

I have seen from other threads that the 'frequency' function can be used to count unique items but I am not sure how to incorporate it into the formula.

I've attached a sample of my data and what I am looking to achieve.

Hope someone can help :thumb

Thanks

Mike

sktneer
10-02-2015, 06:38 AM
Try this monster Array Formula which requires confirmation with Ctrl + Shift + Enter instead of Enter alone.
In E3

=IF($D3=E$2,SUM(--(FREQUENCY(IF($B$2:$B$35=$D3,MATCH($A$2:$A$35&$B$2:$B$35,$A$2:$A$35&$B$2:$B$35,0)),ROW($A$2:$A$35)-ROW($A$2)+1)>0)),SUM(IFERROR((($B$2:$B$35=$D3)+($B$2:$B$35=E$2)>0)*ISNUMBER(MATCH($A$2:$A$35,IF($B$2:$B$35=$D3,$A$2:$A$35),0))*ISNUMBER(MAT CH($A$2:$A$35,IF($B$2:$B$35=E$2,$A$2:$A$35),0))*(FREQUENCY($A$2:$A$35,$A$2: $A$35)>0),0)))
and then copy across and down.

For detail refer to the attached workbook.

mike1984
10-02-2015, 07:25 AM
sktneer.....this is amazing! Thank you so much. I have amended it slightly so that it works if the order number in column A is in a text format. I have just inserted the value() function at the correct point.

I have been after a formula like this for so long. Thank you!

sktneer
10-02-2015, 08:12 AM
You're welcome Mike. Glad it worked for you.
I rarely visit here as you can see I have only 4 posts here but when I visited here this time I found this interesting question that motivated me to construct that formula, don't know whether a shorter version is possible. Happy that it resolved your issue. :)