Pivot Table Prob. w/ multiple consolidation ranges

[stubbsj]

I need a way to use additional "Row" when consolidating multiple ranges. I used the Pivot wizard, selecting "multiple consolidation ranges" which works great if you are only using 1 "Row" value. I need to have 4 "Row" values so I can display my pivot table similar to what I show in the attached file Desired Pivot tab.

If the Pivot Wizard can't be used, how would I construct a Pivot table to get the desired results.

Any help is greatly appreciated!!!!

Jim

[Dionysos]

How about concatenating the first 3 columns of each of your source tables into a new column D. Column D would then result in your new row name. Then you follow the steps you performed to consolidate the ranges into a single pivot. Switch the order of your row labels ("Row" then "Page1") in your pivot construct and you will be close to your desired result.

[stubbsj]

Thanks Dionysos -
I hadn't thought of that. I'll give it a try.

Jim

[mpbalaji007]

How to get the pivot table like stubbsj mentioned in the last tab in his sheet. i really need the excel like that. Please help.

[stubbsj]

Could you be a little more specific in what you are looking to do?

[stubbsj]

You need to use the PivotTable and PivotChart Wizard to create the consolidated pivot table. All parts of the resultant pivot table must reference back to the exact named column headings in each sheet starting with the column "Row". You can use any value in "Row" as long as it is consistent in each spreadsheet.

[mpbalaji007]

Hello Stubbsj,
I read the above thread and i understood that we need to concatenate the 3 columns for the pivot table. But i need it 3 rows, like if i expand CA it shows Los Angeles, Phoenix, and then if i open Phoenix it should show the store #1, store 2 etc. Thats what your desired pivot (last tab) in the spreadsheet you attached. Can you please help me get it without concatenating the columns. It would be really helpful for me.

Thanks much.

[FDibbins]

mpbalaji007, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[stubbsj]

Unfortunately, that is THE limit of a consolidated pivot table. You can only have the 1 row.

Suggest you open up a new thread to see if someone else can help you.