Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table layout
I can't get the table to layout the way I want it to
Data Row Col a b c d e f aa 1 1 1 1 1 1 ab 2 2 2 2 2 2 aa 3 3 3 3 3 3 ab 4 4 4 4 4 4 ac 5 5 5 5 5 5 af 6 6 6 6 6 6 I want to group the rows that are common and sum the colums in like rows. The report result (summing the columns) should look like this a b c d e f aa 4 4 4 4 4 4 ab 6 6 6 6 6 6 ac 5 5 5 5 5 5 af 6 6 6 6 6 6 I do I make this happen in pivot table? Jim Roth |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table layout
Jim:
Given the structure you posted, you won't be able to get a Pivot Table laid out the way you want.....but.... You WILL get that layout if you use Data Consolidation: <data<consolidate Select your data range....click the [ADD] button Use Labels in... Check: Top Row Check: Left Column Click the [OK] button OR If you format your data this way, the Pivot Table will work: Dbl Sgl Value aa a 1 ab a 2 aa a 3 .. . . .. . . .. . . ab f 4 ac f 5 af f 6 Does that help? *********** Regards, Ron XL2002, WinXP "appleknocker" wrote: I can't get the table to layout the way I want it to Data Row Col a b c d e f aa 1 1 1 1 1 1 ab 2 2 2 2 2 2 aa 3 3 3 3 3 3 ab 4 4 4 4 4 4 ac 5 5 5 5 5 5 af 6 6 6 6 6 6 I want to group the rows that are common and sum the colums in like rows. The report result (summing the columns) should look like this a b c d e f aa 4 4 4 4 4 4 ab 6 6 6 6 6 6 ac 5 5 5 5 5 5 af 6 6 6 6 6 6 I do I make this happen in pivot table? Jim Roth |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table layout
Hey Ron...
You can use the Pivot table to get the results that you want... First you have to lable the first column... the Pivot table will not recognize a blank cell as a column or row header... Assuming you have the data laid out in columns/rows "A1:G7", then enter a label name in cell A1 (Call it List Label for this dialogue)... the pivot table will create named values for each column, create the Pivot Table using this range, the Pivot Table will provide you with the list of the column headers to position in the Drop Areas... drag the List Lable to the Rows area of the Pivot Table, then drag each of the remaining columns into the Data Area of the Pivot Table... finally once the Pivot Table is set up, it will be in a column structure, simply drag the "Data" label from the Pivot Table and drag it into the Cloumns Drop Area and you will have the results that you are looking for... -- Thanks for your help - Joe Mac "Ron Coderre" wrote: Jim: Given the structure you posted, you won't be able to get a Pivot Table laid out the way you want.....but.... You WILL get that layout if you use Data Consolidation: <data<consolidate Select your data range....click the [ADD] button Use Labels in... Check: Top Row Check: Left Column Click the [OK] button OR If you format your data this way, the Pivot Table will work: Dbl Sgl Value aa a 1 ab a 2 aa a 3 . . . . . . . . . ab f 4 ac f 5 af f 6 Does that help? *********** Regards, Ron XL2002, WinXP "appleknocker" wrote: I can't get the table to layout the way I want it to Data Row Col a b c d e f aa 1 1 1 1 1 1 ab 2 2 2 2 2 2 aa 3 3 3 3 3 3 ab 4 4 4 4 4 4 ac 5 5 5 5 5 5 af 6 6 6 6 6 6 I want to group the rows that are common and sum the colums in like rows. The report result (summing the columns) should look like this a b c d e f aa 4 4 4 4 4 4 ab 6 6 6 6 6 6 ac 5 5 5 5 5 5 af 6 6 6 6 6 6 I do I make this happen in pivot table? Jim Roth |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table layout
True, Joe (I use that technique at least once a week and didn't think of
it. DOH!) *********** Regards, Ron XL2002, WinXP "Joe Mac" wrote: Hey Ron... You can use the Pivot table to get the results that you want... First you have to lable the first column... the Pivot table will not recognize a blank cell as a column or row header... Assuming you have the data laid out in columns/rows "A1:G7", then enter a label name in cell A1 (Call it List Label for this dialogue)... the pivot table will create named values for each column, create the Pivot Table using this range, the Pivot Table will provide you with the list of the column headers to position in the Drop Areas... drag the List Lable to the Rows area of the Pivot Table, then drag each of the remaining columns into the Data Area of the Pivot Table... finally once the Pivot Table is set up, it will be in a column structure, simply drag the "Data" label from the Pivot Table and drag it into the Cloumns Drop Area and you will have the results that you are looking for... -- Thanks for your help - Joe Mac "Ron Coderre" wrote: Jim: Given the structure you posted, you won't be able to get a Pivot Table laid out the way you want.....but.... You WILL get that layout if you use Data Consolidation: <data<consolidate Select your data range....click the [ADD] button Use Labels in... Check: Top Row Check: Left Column Click the [OK] button OR If you format your data this way, the Pivot Table will work: Dbl Sgl Value aa a 1 ab a 2 aa a 3 . . . . . . . . . ab f 4 ac f 5 af f 6 Does that help? *********** Regards, Ron XL2002, WinXP "appleknocker" wrote: I can't get the table to layout the way I want it to Data Row Col a b c d e f aa 1 1 1 1 1 1 ab 2 2 2 2 2 2 aa 3 3 3 3 3 3 ab 4 4 4 4 4 4 ac 5 5 5 5 5 5 af 6 6 6 6 6 6 I want to group the rows that are common and sum the colums in like rows. The report result (summing the columns) should look like this a b c d e f aa 4 4 4 4 4 4 ab 6 6 6 6 6 6 ac 5 5 5 5 5 5 af 6 6 6 6 6 6 I do I make this happen in pivot table? Jim Roth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
pivot table - New Data | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel | |||
how to delete/clean out the row list in pivot table | Excel Worksheet Functions | |||
Pivot Table Problems | Excel Discussion (Misc queries) |