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,948
    Thanks
    421
    Thanked 1,605 Times in 1,449 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,407
    Thanks
    164
    Thanked 638 Times in 606 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,407
    Thanks
    164
    Thanked 638 Times in 606 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,104
    Thanks
    188
    Thanked 725 Times in 638 Posts

  6. #6
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    848
    Thanks
    211
    Thanked 108 Times in 95 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,976
    Thanks
    156
    Thanked 769 Times in 701 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
  •