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?


--
cscribner
------------------------------------------------------------------------
cscribner's Profile: http://www.excelforum.com/member.php...o&userid=23661
View this thread: http://www.excelforum.com/showthread...hreadid=373311