What if I have two lists in two files each with about 59000 rows but with the
same structures. Multiple Consolidation only seems to allow me to summarize
on one key. I need the resulting pivot to summarize by sales, product group,
and period number. If only I could have more rows in one file !

Maybe you don't have this option, but you can export both lists to csv,
combine in a single file using notepad or similar, and use that file as the
external source for the pivot table.

"Hood" wrote:

What if I have two lists in two files each with about 59000 rows but with the
same structures. Multiple Consolidation only seems to allow me to summarize
on one key. I need the resulting pivot to summarize by sales, product group,
and period number. If only I could have more rows in one file !

I have saved both as csv, then cut and paste and renamed but when I go to
external source and select the csv file it says reference not valid - am I
doing something wrong ?

"Miguel Zapico" wrote:

Maybe you don't have this option, but you can export both lists to csv,
combine in a single file using notepad or similar, and use that file as the
external source for the pivot table.

"Hood" wrote:

What if I have two lists in two files each with about 59000 rows but with the
same structures. Multiple Consolidation only seems to allow me to summarize
on one key. I need the resulting pivot to summarize by sales, product group,
and period number. If only I could have more rows in one file !

To use the file as external data source, you can use the pivot table wizard.
In the step 1 of 3 select "External data source", click next and click "Get
data" in the step 2 of 3.
Now a "Choose Data source" window should appear, select <New Data Source
and click Ok
A "Create New Data Source" appears, put the name that you want in textbox 1,
select "Microsoft Text Driver" in dropdown list 2, click "Connect" and either
use the current directory if the csv file is there or "Select Directory" and
browse to directory with the file. On the dropdown 4 will appear all the csv
files in that directory, choose the one you have created.
The next part of the wizard prompts you to choose columns. It uses the
first row of the csv file, so if your file has no headers it will show the
first row of data. Select all the fields you need, and keep going throught
the wizard, the fields selected there will be available on the pivot table
layout.

This process can be also used with other data origins, so if you prefer to
use a database like Access it will just change the Data Source connection.

"Hood" wrote:

I have saved both as csv, then cut and paste and renamed but when I go to
external source and select the csv file it says reference not valid - am I
doing something wrong ?

"Miguel Zapico" wrote:

Maybe you don't have this option, but you can export both lists to csv,
combine in a single file using notepad or similar, and use that file as the
external source for the pivot table.

"Hood" wrote:

What if I have two lists in two files each with about 59000 rows but with the
same structures. Multiple Consolidation only seems to allow me to summarize
on one key. I need the resulting pivot to summarize by sales, product group,
and period number. If only I could have more rows in one file !