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

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.

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

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

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.