From John Walkenbach's website illustrating Joel Horowitz's technique:
http://j-walk.com/ss/excel/usertips/tip068.htm
With a range in this structure:
Account____Dept_1________Dept_2
Cash___________101___________102
Inventory______201___________202
Equip__________301___________302
<Data><Pivot Table>
Use: Multiple Consolidation Ranges_____Click [Next]
Select: "I will create the page fields"_____Click [Next]
Range: (Select your data)_____Click [Add]_____Click [Next]
Click the [Layout] button
ROW: Drag ROW off the diagram
COLUMN: Drag COLUMN off the diagram
DATA: Leave the VALUE field in this section
Click the [OK] button
Select a location for the Pivot Table_____Click [Finish]
That will create a minimal Pivot Table containing only one cell with a value.
Double-Click on that one value cell
Excel will add a sheet to the workbook with the details of
that cell in a database table format, like this:
Row_________Column____Value
Cash_______Dept_1_____101
Cash ______Dept_2_____102
Inventory__Dept_1_____201
Inventory__Dept_2_____202
Equip______Dept_1_____301
Equip______Dept_2_____302
Then change the column headings.
Is that something you can work with?
Bookmarks