C
cscribner
I'm trying to consolidate data from two external data sources that bring
in product orders & revenue by date.
the first one may look like this:
Date1 | Prod1 | 1 | $500
Date1 | Prod2 | 2 | $150
Date2 | Prod2 | 4 | $300
the second one may look like this:
Date1 | Prod0 | 1 | $100
Date1 | Prod1 | 1 | $500
Date1 | Prod1 | 2 | $1000
Date2 | Prod1 | 1 | $500
Date2 | Prod2 | 1 | $75
And I need to combine them into a single table that looks like this:
Date1 | Prod0 | 1 | $100
Date1 | Prod1 | 4 | $2000
Date1 | Prod2 | 2 | $150
Date2 | Prod1 | 1 | $500
Date2 | Prod2 | 5 | $375
The problem with just summing specific cells is that these external
sources are dynamic, and change according to other settings. The
problem with using a summing a vlookup equation is that the same
combinations can appear multiple times in any single table (see that
Date1 | Prod1 is listed twice in the second table).
Any ideas? of course I want something that will autoupdate--the problem
is having to reconcile these lists manually. I created a pivot table
from each of these tables, but I don't know how to make a new pivot
table based on two other pivot tables. Is that possible?
in product orders & revenue by date.
the first one may look like this:
Date1 | Prod1 | 1 | $500
Date1 | Prod2 | 2 | $150
Date2 | Prod2 | 4 | $300
the second one may look like this:
Date1 | Prod0 | 1 | $100
Date1 | Prod1 | 1 | $500
Date1 | Prod1 | 2 | $1000
Date2 | Prod1 | 1 | $500
Date2 | Prod2 | 1 | $75
And I need to combine them into a single table that looks like this:
Date1 | Prod0 | 1 | $100
Date1 | Prod1 | 4 | $2000
Date1 | Prod2 | 2 | $150
Date2 | Prod1 | 1 | $500
Date2 | Prod2 | 5 | $375
The problem with just summing specific cells is that these external
sources are dynamic, and change according to other settings. The
problem with using a summing a vlookup equation is that the same
combinations can appear multiple times in any single table (see that
Date1 | Prod1 is listed twice in the second table).
Any ideas? of course I want something that will autoupdate--the problem
is having to reconcile these lists manually. I created a pivot table
from each of these tables, but I don't know how to make a new pivot
table based on two other pivot tables. Is that possible?