How to find the most common data combinations

[Excel_apprentice]

I have a list of customers along with their purchases. I would like to see what are the most popular purchase combinations that customer make.
For example in this scenario, the most common is Apple and Orange with 3 customers ordering both.

[AliGW]

You need to mock up an example of what you want. Just giving us the raw data is not enough.

[Excel_apprentice]

Of course, sorry about that. See attached. I'm interested in anything that shows up more than once really, in order of most common.

[MrShorty]

That sounds like "market basket analysis". I know we've had previous conversations here over the years for market basket analysis type problems, and it seems like most of them have been some form of "find an outside utility/app/add-in that does it." If you are not required to program your own MBA algorithm from scratch, then that is probably the easiest. I even seem to recall seeing (for a past version of Excel 2013 I think) something like an optional Power-BI menu/ribbon that had a built in MBA tool integrated into Excel. If you have access to Power BI (or other third party tool), see how to use it to perform the analysis.

I'm not familiar with the algorithms behind MBA, so I would not be able to program it into the spreadsheet. I expect that, if it is necessary to program from scratch and you help us understand the algorithm, we should be able to help with the programming.

If it helps, I put "market basket analysis Excel" into my favorite search engine and this was the first of many hits: https://www.data-mania.com/blog/mark...ysis-in-excel/ Certainly not the only one, nor do I suggest it is the best tutorial, just the first in my results list. There are plenty of others if you want to search for them.

[josephteh]

With possible combinations listed in D1:M2 and Customer Name in C3:C7,
D3=QUOTIENT(COUNTIFS(A2:A14,C3#,B2:B14,D1:M1)+COUNTIFS(A2:A14,C3#,B2:B14,D2:M2),2)

How to find the most common data combinations.png

[Excel_apprentice]

This looks great, however it looks like you manually wrote the different possible combinations - D1:M2. Is that right? In the real data that I want to apply this to, there are 56 different products, rather than the 5 in this spreadsheet, so that wouldn't really be feasible.

[JohnTopley]

A VBA-based solution using a "matrix" of similar concept as Josephteh formula solution.

Please Login or Register  to view this content.

[Excel_apprentice]

Thanks. I've never used a VBA, so not sure how to apply this to my main spreadsheet.

[JohnTopley]

First, there is no need to re-post (quote) the code: it is just "clutter" and wastes resource.

Second, post a fie which represents your actual file format [and anonymise any data]. The code can then be adjusted as needed.

[Bo_Ry]

Please try

With Lambda

Please Login or Register  to view this content.

MS365 Insider

Please Login or Register  to view this content.

[soledad]

Suppose that C2:C6 is List of fruits you have
Fruits
Apple
Orange
Grapefruit
Banana
Grapes
Formula to count the most common number of purchasing any both of fruits

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER
Good luck