combining table data

[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?

[PC]

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="Prod1")*(Sheet1!C1:C3))+SUMPRO
DUCT((Sheet2!A1:A5="Date1")*(Sheet2!B1:B5="Prod1")*(Sheet2!C1:C3))

HTH

PC

"cscribner" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[PC]

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="Prod1")*(Sheet1!C1:C3))+SUMPRO
DUCT((Sheet2!A1:A5="Date1")*(Sheet2!B1:B5="Prod1")*(Sheet2!C1:C3))

HTH

PC

"cscribner" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[PC]

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="Prod1")*(Sheet1!C1:C3))+SUMPRO
DUCT((Sheet2!A1:A5="Date1")*(Sheet2!B1:B5="Prod1")*(Sheet2!C1:C3))

HTH

PC

"cscribner" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[cscribner]

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