+ Reply to Thread
Results 1 to 8 of 8

Product Combinations bought by Customers

  1. #1
    Registered User
    Join Date
    03-24-2019
    Location
    Scottsdale, AZ
    MS-Off Ver
    O365
    Posts
    4

    Product Combinations bought by Customers

    I have a Table that depicts all customers and the products they have purchased. There are about 250 Products and about 500 Customers for total of about 2800 Product Client combination. I am attempting to figure out the various types of combinations of products that are frequently purchased.

    Additionally, I have some standard combinations of products that I have grouped together and would like to see what accounts have that grouping of products.

    How would I accomplish this ?

    Any assistance is appreciated.
    _____________________________________________________________________________________________

    Thank You for the guidance. I have enclosed a sample file and I would like to interrogate the data to see :
    a) What are the frequently purchased combinations of products in order of reducing frequency : Who are the clients buying each combination ?
    b) How many and which clients are buying Combo 1 or Como 2 or Combo 3 etc. in the master list ?
    Attached Files Attached Files
    Last edited by stanstar; 03-24-2019 at 01:24 AM. Reason: Attaching sample File

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Product Combinations bought by Customers

    Hi, welcome to the forum

    Actually, there are potentially 1250000 Prod/Client combos

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-24-2019
    Location
    Scottsdale, AZ
    MS-Off Ver
    O365
    Posts
    4

    Re: Product Combinations bought by Customers

    Thank You Ford for the recommendation. I have updated the thread appropriately.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Product Combinations bought by Customers

    Im thinking that you will need to 1st ID all prod/client combos for this, and I can think of 2 ways to start this off

    1. Create a table with a vertical list of customers and headings for Prods, then use COUNTIFS.
    I started mt table in I1 (empty), with Prod starting in I2 and names in J1
    J2=COUNTIFS($B:$B,$I2,$A:$A,J$1)
    copied down and across as needed

    OR
    use a Pivot Table with Client as Column and Product for rows and Values

    Once you have this summary, you can start doing your extracts

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,585

    Re: Product Combinations bought by Customers

    The problem you describe sounds to me like "market basket analysis" -- a common analysis. It's not an analysis I do, but I am aware of it. A quick internet search for "market basket analysis excel" turned up:
    A tool if not built into Excel, available to Excel users from Microsoft: https://docs.microsoft.com/en-us/sql...ql-server-2014
    This blog post: http://www.data-mania.com/blog/marke...ysis-in-excel/
    This one, whose author included PhD behind his/her name: https://people.revoledu.com/kardi/tu.../MarketBasket/
    And several others. Market basket analysis is a fairly common analysis in many programming languages, and Excel/spreadsheets are no different. You might see if any of these tools/essays are useful for you.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Registered User
    Join Date
    08-02-2012
    Location
    qatar
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Product Combinations bought by Customers

    Hi Stanstar,

    please find attached file, may be could help your requirements ;refer column I & J


    Thanks,

    JDN
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-24-2019
    Location
    Scottsdale, AZ
    MS-Off Ver
    O365
    Posts
    4

    Re: Product Combinations bought by Customers

    Thanks JDN,

    What do Columns I and J provide ? I am uncertain I can interpret the formula. Please help.

  8. #8
    Registered User
    Join Date
    03-24-2019
    Location
    Scottsdale, AZ
    MS-Off Ver
    O365
    Posts
    4

    Re: Product Combinations bought by Customers

    Thanks MrShorty,

    I will review and see if it assists some.

+ 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. [SOLVED] How to find customers which purchase particular product
    By devawad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2018, 01:00 AM
  2. Replies: 44
    Last Post: 03-17-2016, 03:39 PM
  3. [SOLVED] How to display customers that have not bought an item in a pivot report
    By justmeok in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 03-28-2015, 06:20 PM
  4. 32 bit vba excel product bought wont run on excel in 64 bit computer
    By chibiks in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2015, 03:42 PM
  5. Product of Combinations
    By clattenburg cake in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-22-2013, 12:10 PM
  6. Excel function - product,combinations mix
    By bbog in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2012, 12:15 PM
  7. Replies: 5
    Last Post: 01-16-2010, 01:17 AM

Tags for this Thread

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