# Thread: Shopping basket or items most frequently picked together (large data)

1. ## Shopping basket or items most frequently picked together (large data)

Hi,

I am looking for vba code that would look data in two columns (Col1 "order" and Col2 "Items") then return items frequently picked together on multiple orders. In a way that shopping basket analysis is that how many time customer shopped similar items. such as.

order items
01 A
01 B
01 C
02 B
02 A
03 A
03 C
04 A
04 B
04 C

SO A.B.C = 2 TIMES , A.B = 3 TIMES , AND A.C = 3 TIMES

In my data the order number len is 7-8 and items len is 6-7. Thank you.

2. XX,

Not quite sure how to tackle this in VBA however, here's a possible approach using a Pivot table then adding some formulas. Of course this could be quite complicated based on the number of items you are tracking and the combinations you are interested in.

Here's my test file with the formulas: ShoppingBasket-RGV1.xlsx

Another approach would be using SumProduct but I can't wrap my mind around that just yet. Maybe some of the Lounge's SumProduct experts will chime in on this approach.

HTH

3. Hi

Maybe you also need to know how many different customers choose any particular combination??
(Maybe it's only me that orders Malibu rum, milk, pork pies, rye bread and edam cheese together - and although there might be 100 picking that combination, it's really just 1 - ME!)

..so maybe you need a Customer ID as well?

zeddy

4. Hi

..just for info, Excel has a Shopping Basket Analysis tool available
(if you have Excel 2016 and Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2)
zShopping.GIF

https://msdn.microsoft.com/en-GB/library/dn282369.aspx

zeddy

5. Originally Posted by xxsinghxx
return items frequently picked together on multiple orders
I looked into this a few years ago, and I have one important recommendation before you get into crunching the numbers. If you analyze for "items" as you say, you will end up with two problems:
1) Very unwieldy and unstable data, if you can get the computations to perform at all;
2) Nearly useless data, because item-level is too detailed to provide useful business info.

I recommend you do the analysis on 'product group' or 'category', which will be much more workable and useful.

For example, category = footwear, product group = mens' formal shoes, item = patent leather tan, size 10.

You are unlikely to find the info about what was bought with 'patent leather tan, size 10' useful, whereas what other product groups [eg mens' socks] or categories [eg gloves] were bought with footwear or mens' formal shoes is info you can use in many ways to improve business.

6. XX,

Here is some code that will do what I think you want. It will count the same item combinations for the different orders. The unique combination list is in column D and their counts in column E. Column A is formatted as text to preserve the leading 0 in the order numbers.

COMBO1.png

There is no limit to the number of orders or items. The code will automatically sort them so they do not have to be in any order

HTH,
Maud

Note: as an afterthought, I should have made the items case insensitive