combining table data
 

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

A two-tier solution will accomplish this

First create dynamic named ranges for the tables. I believe Debra Dalgleish
has information on this at her site, which you can find using Google.
Though assuming table 1 for example is in Sheet1 cells A1:C3

Then

Insert/Name/Define

Table1Date =OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1) (the "-1 adjusts
for a header row)
Table1Prod =OFFSET(Sheet1!$B$1,0,0,COUNTA($A:$A))
Or =OFFSET(Sheet1!$A$1,1,1,COUNTA($A:$A))
Or =OFFSET(Sheet1!$A$1,MATCH("productcolheader",$1:$1 ,0)-1,0,COUNTA($A:$A))
If you use this formula for the range the order of the table won't matter.

I'll assume you can derive the remaining named ranges

Then use SUMPRODUCT to get the sum of the data.

For the tables you provided it would look like

=SUMPRODUCT((Table1Date="Date1")*(Table1Prod="Prod 1")*(Sheet1!C1:C3))+SUMPRO
DUCT((Sheet2!A1:A5="Date1")*(Sheet2!B1:B5="Prod1") *(Sheet2!C1:C3))

HTH

PC

"cscribner" wrote in message
...

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

Thank you for your generous response. I have to admit that I didn't
follow it completely--my understanding of the sumproduct function is
that it returns a numeric sum of stuff, not a table array of text and
numeric data. I'd love to delve into this stuff and understand it, but
for the meantime, I found out that references to pivot tables are
pretty dynamic, so I stuck with my original plan of one pivot table for
each of these external sources, and then summing the two references in
an end-table.

thanks again!

Craig


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