+ Reply to Thread
Results 1 to 7 of 7

items most frequently picked together

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    Powell, oh
    MS-Off Ver
    Excel 2007
    Posts
    7

    items most frequently picked together

    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.
    Last edited by xxsinghxx; 05-28-2016 at 08:31 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: items most frequently picked together

    Could you please provide a workbook with some sample data. I have a report I do that does precisely this and it involves pivot tables, however I'm going to have to see some source data to work it out for you.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    01-17-2013
    Location
    Powell, oh
    MS-Off Ver
    Excel 2007
    Posts
    7
    Quote Originally Posted by dflak View Post
    Could you please provide a workbook with some sample data. I have a report I do that does precisely this and it involves pivot tables, however I'm going to have to see some source data to work it out for you.
    Please check this link for sample data. Thank you.
    http://expirebox.com/download/19447ef765a2afbfd738b76e687862ee.html

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: items most frequently picked together

    Please attach the spreadsheet to this post, I cannot download the link.

  5. #5
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: items most frequently picked together

    Attached is the file from his link.
    Hope you can give a solution.
    Attached Files Attached Files

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: items most frequently picked together

    You may want to limit your analysis to recent orders. The ~250 orders exploded into 11.5K lines of data and took about a minute and a quarter to run.

    I am a big believer in letting Excel do the heavy lifting and use VBA to bolt and stitch the pieces together in Frankenstein-like fashion. In this case I used pivot tables. On the pivots page, the pivot table in column A gives me a unique list of orders.

    The pivot table in columns C and D is the working pivot table. The program goes down the list of orders and sets the filter in cell D1 to the order number. This brings up a list of things ordered together.

    I use VB code to do a double loop against the working pivot table to pair the items and put them into the Excel table in Columns K:M. So if A is ordered with B then B is ordered with A. The COUNTIFS formula in Column M counts up how many times that particular pairing happens.

    The pivot table in column F:G is the "report" It gets the top 5 values for the item selected in Cell G1. About the only issue with the drop down is that it is not in alphabetical order. There is probably some coding that can be done for that.
    Attached Files Attached Files

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: items most frequently picked together

    I suppose this could be taken one step further and that would be to get a unique list of items (similar to the unique list of orders), loop through them and set the filter in Cell G1. Then copy the item and the top 5 results to a table somewhere else.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Market basket analysis formula
    By troygolding33 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-10-2016, 02:53 PM
  2. Replies: 5
    Last Post: 04-12-2013, 05:48 PM
  3. Replies: 4
    Last Post: 03-19-2012, 01:31 AM
  4. Matching prices for shopping items
    By samlim in forum Excel General
    Replies: 3
    Last Post: 01-04-2010, 04:20 AM
  5. [SOLVED] how to make a chart for frequently used items
    By teetrinity in forum Excel General
    Replies: 0
    Last Post: 02-17-2006, 07:25 PM
  6. [SOLVED] 'add-to-basket' function
    By A little Help in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-06-2005, 12:06 PM
  7. [SOLVED] basket with fruits : numbers and formula
    By rbus in forum Excel General
    Replies: 4
    Last Post: 01-25-2005, 05:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1