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

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
>

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

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