Hello, I found your task very interesting, so I tried to implement it. Unfortunately, you didn't tell me in detail what form your data is in, so I made something up myself. It probably doesn't fit exactly. But maybe it will give you some inspiration.
I made a table with customers and entered the products purchased in the same row.
I have prepared 11 product columns. However, columns can also be added if necessary. If a product is purchased twice by a customer, this is not taken into account in the analysis. Each customer only appears once in my list. If customers have multiple purchases, this is currently not taken into account.
The 2-purchase combinations are analysed in the Analysis worksheet. The sheet is prepared for 99 different products. If there are more, this must be extended manually. Until then, it is dynamic and does not need to be edited.
I did not dare to analyse the combination of 3 products. Depending on how many products are analysed, there are a lot of combinations to analyse. In my opinion, this is no longer a task for a spreadsheet but for a Python script.
The most frequent product combinations are then displayed in the TOP x table. At least the best 10 results are displayed. If there are other TOP results that are just as frequent as those in row 11, these are also displayed. That was quite tricky.
I would be pleased if you can do something with my analysis sheet. If you have any further comments, please contact me again.
Accepted Solutions