Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,865
    Thanks
    417
    Thanked 1,577 Times in 1,428 Posts
    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.

    basketpt.PNG

    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,348
    Thanks
    162
    Thanked 623 Times in 591 Posts
    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. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,348
    Thanks
    162
    Thanked 623 Times in 591 Posts
    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

    here's a link for more info:
    https://msdn.microsoft.com/en-GB/library/dn282369.aspx

    zeddy

  5. #5
    Super Moderator
    Join Date
    Jun 2011
    Location
    New England
    Posts
    5,080
    Thanks
    187
    Thanked 712 Times in 626 Posts

  6. #6
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    828
    Thanks
    209
    Thanked 106 Times in 93 Posts
    Quote Originally Posted by xxsinghxx View Post
    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.
    Lugh.
    ~
    Dell Alienware Aurora R6 (new 2017)
    Windows 10 Home x64 1703; Office 365 x32
    GeForce GTX 1060; 16GB DDR4 2400
    256G SSD, 1TB HD

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,884
    Thanks
    147
    Thanked 734 Times in 666 Posts
    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •