How to used pivot table to aggregate this request?

Hello,

Just wondering if this is even possible before i take it into a VBA format.

Let's say I have multiple rows of data and each row is consists of 4 columns

Column 1: Product name
Column 2: Supplier A ID
Column 3: Suplier B ID (if even available)
Column 4: How much do we hold currently of this product

This request does NOT make sense but here goes...

I want to pivot by supplier ID and how much product we hold from each supplier. Sounds easy I know. But here's the difficult part:

I need to combine the amount for duplicate supplier between supplier A and B (to be conservative : business requirement)

What does this really mean?

For example if product A only has 1 supplier , with the supplier ID of "Walmart" and we hold $5000, then the pivot should show Walmart and $5000. (easy enough)

But if product B has 2 suppliers (A&B), with Supplier A ID of "costco" and supplier B ID of "Walmart" and we hold $1000 of product B... (keep in mind we have no way of distinguishing how much each supplier contributes TO that 1000, and we don't care right now)

I want to see in some kind of pivot that Walmart: 6000 (5000+1000), Costco: 1000....and so on.

I understand that this is not accurate at all, but this will, from a conservative standpoint, shows which supplier we hold goods with the most.


If you pivot, how can you combine any duplicate ID betwen 2 separate columns without making 1 a subcolumn? I don't want 2 lines of walmart showiong 5000 and 1000.



Hopefully this is understandable ., thanks for your time!

Thanks! i will upload them later on tonight when i get home.

So each row is a unique product/product code. We have multiple columns because each product could have at least one (or multiple suppliers)

And the goal is to figure out what is the exposure to each supplier from a conservative standpoint.

OK here it is, thanks for looking guys!

My proposal by the addition of Power Query

looking at the results, it definitely is what i'm looking for, but as far as how to create it, i'll have to google up power query since i can't seem to recreate what you did haha. thank you sir, you have lead the way for me!!

View a short video with advice on how to do:
https://youtu.be/riCI0QkjkaM

Thanks so much!

didn't know there is this power pivot here. Seems like I'll need Admin permission to add this add-in to excel at work...let me see how I can get around to it. thanks!!!

The addition of Power Query is completely free version for Excel 2010. Enter keywords "Add in for Excel 2010" into a search engine and further follow the instructions.