+ Reply to Thread
Results 1 to 9 of 9

Automated sorting and summing of products per product category (based on client number)

  1. #1
    Registered User
    Join Date
    09-06-2017
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    5

    Automated sorting and summing of products per product category (based on client number)

    Hi there,

    So, let me try to explain the situation. I can create a dump file from SAP in Excel, which has a.o. the following columns:
    • Column A: Customer number
    • Column B: Customer name
    • Column C: Customer country
    • Column D: Product category
    • Column E: Product number
    • Column F: Product name
    • Column G: Cartons sold
    The excel file in attachment is a dummy file, heavily simplified, but should give you an idea.

    The idea would be to have an up-to-date overview such as shown in the tabs "123", "456" and "789", which I've created manually for now.

    My dream scenario would be that I fill in the client number in Cell B2 in the tab "TEMPLATE" and then automatically everything else appears. So it looks just as in the tabs "123", "456" and "789". That means:
    • Cell B3: Customer name (easy VLOOKUP)
    • Cell B4: Customer country (easy VLOOKUP)
    • Under Product category M, all product numbers that fall under said category should be listed. With a simple VLOOKUP the Product Name can be added in column B. Then I'd like to have the sum of all cartons sold for said reference in column C.
    • Similar for all other Product categories.


    The workaround I have now is to simply filter or to make a pivot table. But this is obviously not ideal. Furthermore, the actual tab "TEMPLATE" is much more advanced than the one attached here, with dozens of columns and rows more and multiple formulas. So I'd really like a solution that automatically feeds the green cells in the sheet TEMPLATE in the excel sheet attached.

    Not sure if relevant, but for the sake of completeness:
    • Assume roughly 50 customer numbers/names
    • Assume roughly 10 product categories
    • Assume roughly 300 products
    • Assume multiple 1000s of lines in the DATA dump

    Though I'm quite familiar with pivot tables, VLOOKUP, HLOOKUP, INDEX, MATCH, IF, SUMIF, ... I can't seem to find an automated solution for this particular problem. Any suggestion will be highly appreciated! Please don't hesitate to reply should you need further information.

    Cheers,
    Tom
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Automated sorting and summing of products per product category (based on client number

    with a pivot table.

    Maybe something like this
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    09-06-2017
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    5

    Re: Automated sorting and summing of products per product category (based on client number

    Hi oeldere,

    Thanks for the fast reply. Yes, I know that a pivot table is a solution to get the data sorted , but as explained, manual intervention would still be required then. I'd need to copy/paste the various to the other tab "TEMPLATE", which - in the real spreadsheet - is much more complicated. So I'm really looking for a solution that doesn't require a pivot table and really is fully "dummy proof" --> fill in client number and get all information correctly filled in the TEMPLATE sheet.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Automated sorting and summing of products per product category (based on client number

    And what if there are more than 2 options on each product.

    Than you template sheet will not work.

  5. #5
    Registered User
    Join Date
    09-06-2017
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    5

    Re: Automated sorting and summing of products per product category (based on client number

    What do you mean by "more than 2 options on each product"?

  6. #6
    Registered User
    Join Date
    09-06-2017
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    5

    Re: Automated sorting and summing of products per product category (based on client number

    If you mean that 1 product can be listed in more than 1 product category, that is not the case. By default it can only fall under 1 product category.
    So at least we've got that going for us

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Automated sorting and summing of products per product category (based on client number

    You have only A9 and a10 for the results

  8. #8
    Registered User
    Join Date
    09-06-2017
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    5

    Re: Automated sorting and summing of products per product category (based on client number

    I've put in a limited number of products per product category and per client for the moment. So in this scenario only A9 would be sufficient and would need to be filled. In real life there will be many more.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Automated sorting and summing of products per product category (based on client number

    Of course, but you want it to be dummy proof, so members has to change the formula etc.

    In that case it is easier to learn pivot table (to my opinion).

+ 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. Generate a list of products based on what category it's assigned to
    By kitpierce in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-14-2016, 05:00 PM
  2. Replies: 8
    Last Post: 07-01-2016, 02:07 AM
  3. Replies: 6
    Last Post: 03-05-2016, 02:42 PM
  4. Replies: 4
    Last Post: 12-16-2012, 04:33 PM
  5. Sorting data based on product number
    By Mark.Small in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2012, 01:33 AM
  6. Replies: 0
    Last Post: 08-28-2005, 11:07 AM
  7. [SOLVED] summing an array by product category
    By BLW in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-18-2005, 07:06 PM

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