alternative to pivot tables

[tkaplan]

I have an excel sheet (attached) with the following columns:

YEAR, PERIOD, REGION, CENTER, ME1, ME2, TOTAL, COLLECT

The year can be any year, period is 1 to 13, four regions (east, west, south, midwest) and approximately 500 center. me1, me2, total, and collect are numeric values.

I need to display the data like this:

Year Header: Year2005
Period Header: Period1
Region Header: Region1
Center1 me1 me2 total collect
Center2 me1 me2 total collect
Center3 me1 me2 total collect
Totals: me1 me2 total collect
Percent: me1 me2 total collect
Region Header: Region2
Center1 me1 me2 total collect
Center2 me1 me2 total collect
Totals: me1 me2 total collect
Percent: me1 me2 collect

this would repeat itself for each period based on the amount of centers in that region. in example above it would be 3 centers in region1, and 2 in region 2.
The totals row needs to total all of the centers in that region.
the percent row needs to take the percentage of total of me1 over total of total, me2 over total and collect over total.

so sample data:
2005
Period 1
West
C4 100 150 250 50
C9 35 245 280 100
Totals: 135 395 530 150
Percent: 25 75 28

So I tried doing this in a pivot table but that does not allow you to put the percentage row. I'm hoping there is a macro that i can write to just format the actual data into the way i need it displayed.

Any help would be REALLY appreciated.

tkaplan

[shades]

Does this help? Done with XL PivotTable (Sheet2), no VBA.

[tkaplan]

I need the percent to be just for the region totals, not on a center basis.
Any other suggestions?

[shades]

Just drag the center out of the pivot table then.

Maybe I am missing something (not unusual ).

[tkaplan]

i need to have the centers dollar amount there. then the total for the region as a subtotal of all centers and a percentage for the total region.

for example:

region: West
center1:.......C4....100....150....250....50
cneter2:.......C9....35......245....280....100
RegionTot:...........135....395.....530...150
PercentForRegion:..25.....75................28

does this make sense?