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

[xxsinghxx]

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.

 

[xxsinghxx]

Ombir, have you had a chance to look at this request?

 

[Ombir]

I am trying to figure out something but total combinations are getting high which hangs up the excel.

There are duplicate items for some orders in the file provided.

Ex: For order no 35619501, item count for 102842 is 100.

How do you want to count them in finding combinations or we can safely remove duplicates items for each order ??

 

[xxsinghxx]

If the duplicate entries are in one particular order then I think it would be safe to remove duplicate. But if they are repeated in different orders then we need to leave those so we can get the commonly ordered items.

 

[Ombir]

Hi Singh,

I'm afraid this problem isn't doable in VBA. MickG was right. With your sample data, I'm getting around 20 unique items for some of the orders which generates 2^20 possible combinations for only that particular order. Imagine how many orders will have around 20 or more unique items. That's why excel hangs up everytime.

I would be interested to know if you'll ever found solution to this problem in excel.

 

[Ombir]

Hi Singh,

Can you send the complete excel file ? I just able found some solution but not with excel and can provide you frequently picked items if you need.

 

[xxsinghxx]

Here you go.

Download-Link:
http://expirebox.com/download/<wbr>35c04f477dc42c51b41029422572d1<wbr>a2.html

 

[Ombir]

I added the above code in the new module. when I run it then excel stops responding. I have numeric data in column "A" and "B".

Order Items

12345678 1234567

As you mentioned your data consists of numeric fields but now item contains Number, Special characters and Alphabets. I'm afraid this is getting more complex with this type of data. If complete data is in format which you've shared previously then please share.