tkaplan
11-15-2005, 10:04 AM
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
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