Problem with fields in PivotTable with multiple consolidation rang

[Guest]

Hi,

I wanted to do a pivot table from two Excel lists on different sheets (Asia,
Europe, etc.). But when I select "Multiple consolidation ranges" as the
source, the column labels are not recognized as fields. The fields I get
are: Row, Column and Value. This happens even when I choose just one list
with "Multiple consolidation ranges".

For example, if my source list looks like:
Product Country Price
A US 10
A CA 12
B US 20

The fields I get are: Row (A, B), Column (Country, Price) and Value (the
rest...). So I can't group by country.

What I want, and what I get when selecting "Excel list or database", is
obviously the fields: Name, Country and Price.

Of course, I could merge the sheets in one big table and add a "continent"
field, but it seems "Multiple consolidation ranges" just doesn't work like it
should... Any idea?

Thanks

 

[Myrna Larson]

I think you are misunderstanding what a consolidation range is and how it
differs from a "list" or database.

A consolidation range is a pivot or table with one row field, one column
field, and one data field.

It is equivalent to a pivot table generated from a list with 3 fields (field
names unknown to Excel): the row headings are the unique *entries* from the
first field (not field names); the column headings are the unique *entries*
from a second field (not field names); the values in a the data field are the
values in a third field.

Since Excel doesn't know what the original field names were, it uses Row,
Column, and Value.

IOW, if you say your data is a consolidation range rather than a list, Excel
treats it as the pivot table that would result from an Excel "list" that looks
like this:

Row Column Value
A Country US
A Price 10
A Country CA
A Price 12
B Country US
B Price 20

I've fiddled around with trying to create the pivot table in a 2nd workbook,
using the External data option but couldn't get it to work. Maybe you can play
around with that.

I think the path of least resistance is to combine the two tables into one
list, as you propose. Adding a Continent field would eliminate the need to
group the countries into Europe and Asia.

I guess your fundamental problem is that you should have just one list, with a
continent field, then filter by continent to generate the two lists you have
now.

 

[Guest]

Thanks for your answer. Well, if this is by design, I don't really see the
point. Anyway, it would be nice to be able to create a pivot table from
multiple ranges with the same field structure, as I wanted.

Cheers