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,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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
    2,829
    Thanks
    136
    Thanked 482 Times in 459 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
    2,829
    Thanks
    136
    Thanked 482 Times in 459 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
    4,751
    Thanks
    171
    Thanked 650 Times in 573 Posts

  6. #6
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    620
    Thanks
    166
    Thanked 77 Times in 68 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.
    ~
    Windows 10 Pro x64 1607; Office 2016 (365 Home) x32; Win Defender, MBAM Pro

    ASRock H97 Anniversary; Xeon E3-1231V3 (like i7)
    Gigabyte GeForce GTX 970; 12GB Crucial DDR3 1600
    Logitech MX Master mouse; Roccat Isku kb

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 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
  •