software

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 RothJim:

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:

lt;datagt;lt;consolidategt;
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:
DblSglValue
aa a1
ab a2
aa a3
....
....
....
ab f4
ac f5
af f6

Does that help?
***********
Regards,
Ron

XL2002, WinXPquot;appleknockerquot; wrote:

gt; I can't get the table to layout the way I want it to
gt;
gt; Data
gt;
gt; Row Col
gt; a b c d e f
gt; aa 1 1 1 1 1 1
gt; ab 2 2 2 2 2 2
gt; aa 3 3 3 3 3 3
gt; ab 4 4 4 4 4 4
gt; ac 5 5 5 5 5 5
gt; af 6 6 6 6 6 6
gt;
gt; I want to group the rows that are common and sum the colums in like rows.
gt; The report result (summing the columns) should look like this
gt;
gt; a b c d e f
gt; aa 4 4 4 4 4 4
gt; ab 6 6 6 6 6 6
gt; ac 5 5 5 5 5 5
gt; af 6 6 6 6 6 6
gt;
gt; I do I make this happen in pivot table?
gt;
gt; Jim Roth
gt;

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 quot;A1:G7quot;, 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 quot;Dataquot; 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 Macquot;Ron Coderrequot; wrote:

gt; Jim:
gt;
gt; Given the structure you posted, you won't be able to get a Pivot Table laid
gt; out the way you want.....but....
gt;
gt; You WILL get that layout if you use Data Consolidation:
gt;
gt; lt;datagt;lt;consolidategt;
gt; Select your data range....click the [ADD] button
gt; Use Labels in...
gt; Check: Top Row
gt; Check: Left Column
gt; Click the [OK] button
gt;
gt; OR
gt;
gt; If you format your data this way, the Pivot Table will work:
gt; DblSglValue
gt; aa a1
gt; ab a2
gt; aa a3
gt; ...
gt; ...
gt; ...
gt; ab f4
gt; ac f5
gt; af f6
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;appleknockerquot; wrote:
gt;
gt; gt; I can't get the table to layout the way I want it to
gt; gt;
gt; gt; Data
gt; gt;
gt; gt; Row Col
gt; gt; a b c d e f
gt; gt; aa 1 1 1 1 1 1
gt; gt; ab 2 2 2 2 2 2
gt; gt; aa 3 3 3 3 3 3
gt; gt; ab 4 4 4 4 4 4
gt; gt; ac 5 5 5 5 5 5
gt; gt; af 6 6 6 6 6 6
gt; gt;
gt; gt; I want to group the rows that are common and sum the colums in like rows.
gt; gt; The report result (summing the columns) should look like this
gt; gt;
gt; gt; a b c d e f
gt; gt; aa 4 4 4 4 4 4
gt; gt; ab 6 6 6 6 6 6
gt; gt; ac 5 5 5 5 5 5
gt; gt; af 6 6 6 6 6 6
gt; gt;
gt; gt; I do I make this happen in pivot table?
gt; gt;
gt; gt; Jim Roth
gt; gt;

True, Joe (I use that technique at least once a week and didn't think of
it. DOH!)

***********
Regards,
Ron

XL2002, WinXPquot;Joe Macquot; wrote:

gt; Hey Ron...
gt;
gt; You can use the Pivot table to get the results that you want... First you
gt; have to lable the first column... the Pivot table will not recognize a blank
gt; cell as a column or row header...
gt; Assuming you have the data laid out in columns/rows quot;A1:G7quot;, then enter a
gt; label name in cell A1 (Call it List Label for this dialogue)... the pivot
gt; table will create named values for each column, create the Pivot Table using
gt; this range, the Pivot Table will provide you with the list of the column
gt; headers to position in the Drop Areas... drag the List Lable to the Rows
gt; area of the Pivot Table, then drag each of the remaining columns into the
gt; Data Area of the Pivot Table... finally once the Pivot Table is set up, it
gt; will be in a column structure, simply drag the quot;Dataquot; label from the Pivot
gt; Table and drag it into the Cloumns Drop Area and you will have the results
gt; that you are looking for...
gt; --
gt; Thanks for your help -
gt; Joe Mac
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Jim:
gt; gt;
gt; gt; Given the structure you posted, you won't be able to get a Pivot Table laid
gt; gt; out the way you want.....but....
gt; gt;
gt; gt; You WILL get that layout if you use Data Consolidation:
gt; gt;
gt; gt; lt;datagt;lt;consolidategt;
gt; gt; Select your data range....click the [ADD] button
gt; gt; Use Labels in...
gt; gt; Check: Top Row
gt; gt; Check: Left Column
gt; gt; Click the [OK] button
gt; gt;
gt; gt; OR
gt; gt;
gt; gt; If you format your data this way, the Pivot Table will work:
gt; gt; DblSglValue
gt; gt; aa a1
gt; gt; ab a2
gt; gt; aa a3
gt; gt; ...
gt; gt; ...
gt; gt; ...
gt; gt; ab f4
gt; gt; ac f5
gt; gt; af f6
gt; gt;
gt; gt; Does that help?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;appleknockerquot; wrote:
gt; gt;
gt; gt; gt; I can't get the table to layout the way I want it to
gt; gt; gt;
gt; gt; gt; Data
gt; gt; gt;
gt; gt; gt; Row Col
gt; gt; gt; a b c d e f
gt; gt; gt; aa 1 1 1 1 1 1
gt; gt; gt; ab 2 2 2 2 2 2
gt; gt; gt; aa 3 3 3 3 3 3
gt; gt; gt; ab 4 4 4 4 4 4
gt; gt; gt; ac 5 5 5 5 5 5
gt; gt; gt; af 6 6 6 6 6 6
gt; gt; gt;
gt; gt; gt; I want to group the rows that are common and sum the colums in like rows.
gt; gt; gt; The report result (summing the columns) should look like this
gt; gt; gt;
gt; gt; gt; a b c d e f
gt; gt; gt; aa 4 4 4 4 4 4
gt; gt; gt; ab 6 6 6 6 6 6
gt; gt; gt; ac 5 5 5 5 5 5
gt; gt; gt; af 6 6 6 6 6 6
gt; gt; gt;
gt; gt; gt; I do I make this happen in pivot table?
gt; gt; gt;
gt; gt; gt; Jim Roth
gt; gt; gt;