software

Excel 2003 on Windows XP Pro SP2

Sheet1 has columns for ClientName, TransAmount, AgencyComm and ProdComm.

I am trying to use a pivot table to aggregate the data by ClientName.

I can get a table that gives me all of the information, but isn't laid out
logically. I get 3 rows for each ClientName, with Sum of TransAmount, Sum
of AgencyComm and Sum of ProdComm on separate rows.

For example, current results:

ClientName Data Total
Alpha Sum of TransAmount 100
Sum of AgencyComm 20
Sum of ProdComm 10
Beta Sum of TransAmount 50
Sum of AgencyComm 5
Sum of ProdComm 2
etc.

I would like to get each sum in a separate column.

Desired results:

ClientName Sum of TransAmount Sum of AgencyComm Sum of ProdComm
Alpha 100 20 10
Beta 50 5 2
Gamma 200 50 25
Delta 150 75 30

Is this possible with a pivot table?

Thanks,
Tim C
Drag the Data field button onto the cell that contains the word Total,
as shown he

www.contextures.com/xlPivot02.html

Tim C wrote:
gt; Excel 2003 on Windows XP Pro SP2
gt;
gt; Sheet1 has columns for ClientName, TransAmount, AgencyComm and ProdComm.
gt;
gt; I am trying to use a pivot table to aggregate the data by ClientName.
gt;
gt; I can get a table that gives me all of the information, but isn't laid out
gt; logically. I get 3 rows for each ClientName, with Sum of TransAmount, Sum
gt; of AgencyComm and Sum of ProdComm on separate rows.
gt;
gt; For example, current results:
gt;
gt; ClientName Data Total
gt; Alpha Sum of TransAmount 100
gt; Sum of AgencyComm 20
gt; Sum of ProdComm 10
gt; Beta Sum of TransAmount 50
gt; Sum of AgencyComm 5
gt; Sum of ProdComm 2
gt; etc.
gt;
gt; I would like to get each sum in a separate column.
gt;
gt; Desired results:
gt;
gt; ClientName Sum of TransAmount Sum of AgencyComm Sum of ProdComm
gt; Alpha 100 20 10
gt; Beta 50 5 2
gt; Gamma 200 50 25
gt; Delta 150 75 30
gt;
gt; Is this possible with a pivot table?
gt;
gt; Thanks,
gt; Tim C
gt;
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlThank you, Debra. I've looked everywhere for that, but it's one of those
things that you don't know what terms to use in your search. Of course I
should have realized that your website is the first place to look for pivot
table answers.

Thanks,
Tim C

quot;Debra Dalgleishquot; gt; wrote in message
...
gt; Drag the Data field button onto the cell that contains the word Total, as
gt; shown he
gt;
gt; www.contextures.com/xlPivot02.html
gt;
gt; Tim C wrote:
gt;gt; Excel 2003 on Windows XP Pro SP2
gt;gt;
gt;gt; Sheet1 has columns for ClientName, TransAmount, AgencyComm and ProdComm.
gt;gt;
gt;gt; I am trying to use a pivot table to aggregate the data by ClientName.
gt;gt;
gt;gt; I can get a table that gives me all of the information, but isn't laid
gt;gt; out logically. I get 3 rows for each ClientName, with Sum of
gt;gt; TransAmount, Sum of AgencyComm and Sum of ProdComm on separate rows.
gt;gt;
gt;gt; For example, current results:
gt;gt;
gt;gt; ClientName Data Total
gt;gt; Alpha Sum of TransAmount 100
gt;gt; Sum of AgencyComm 20
gt;gt; Sum of ProdComm 10
gt;gt; Beta Sum of TransAmount 50
gt;gt; Sum of AgencyComm 5
gt;gt; Sum of ProdComm 2
gt;gt; etc.
gt;gt;
gt;gt; I would like to get each sum in a separate column.
gt;gt;
gt;gt; Desired results:
gt;gt;
gt;gt; ClientName Sum of TransAmount Sum of AgencyComm Sum of ProdComm
gt;gt; Alpha 100 20 10
gt;gt; Beta 50 5 2
gt;gt; Gamma 200 50 25
gt;gt; Delta 150 75 30
gt;gt;
gt;gt; Is this possible with a pivot table?
gt;gt;
gt;gt; Thanks,
gt;gt; Tim C
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Excel FAQ, Tips amp; Book List
gt; www.contextures.com/tiptech.html
gt;
You're welcome! Thanks for letting me know that it helped.

Tim C wrote:
gt; Thank you, Debra. I've looked everywhere for that, but it's one of those
gt; things that you don't know what terms to use in your search. Of course I
gt; should have realized that your website is the first place to look for pivot
gt; table answers.
gt;
gt; Thanks,
gt; Tim C
gt;
gt; quot;Debra Dalgleishquot; gt; wrote in message
gt; ...
gt;
gt;gt;Drag the Data field button onto the cell that contains the word Total, as
gt;gt;shown he
gt;gt;
gt;gt; www.contextures.com/xlPivot02.html
gt;gt;
gt;gt;Tim C wrote:
gt;gt;
gt;gt;gt;Excel 2003 on Windows XP Pro SP2
gt;gt;gt;
gt;gt;gt;Sheet1 has columns for ClientName, TransAmount, AgencyComm and ProdComm.
gt;gt;gt;
gt;gt;gt;I am trying to use a pivot table to aggregate the data by ClientName.
gt;gt;gt;
gt;gt;gt;I can get a table that gives me all of the information, but isn't laid
gt;gt;gt;out logically. I get 3 rows for each ClientName, with Sum of
gt;gt;gt;TransAmount, Sum of AgencyComm and Sum of ProdComm on separate rows.
gt;gt;gt;
gt;gt;gt;For example, current results:
gt;gt;gt;
gt;gt;gt;ClientName Data Total
gt;gt;gt;Alpha Sum of TransAmount 100
gt;gt;gt; Sum of AgencyComm 20
gt;gt;gt; Sum of ProdComm 10
gt;gt;gt;Beta Sum of TransAmount 50
gt;gt;gt; Sum of AgencyComm 5
gt;gt;gt; Sum of ProdComm 2
gt;gt;gt;etc.
gt;gt;gt;
gt;gt;gt;I would like to get each sum in a separate column.
gt;gt;gt;
gt;gt;gt;Desired results:
gt;gt;gt;
gt;gt;gt;ClientName Sum of TransAmount Sum of AgencyComm Sum of ProdComm
gt;gt;gt;Alpha 100 20 10
gt;gt;gt;Beta 50 5 2
gt;gt;gt;Gamma 200 50 25
gt;gt;gt;Delta 150 75 30
gt;gt;gt;
gt;gt;gt;Is this possible with a pivot table?
gt;gt;gt;
gt;gt;gt;Thanks,
gt;gt;gt;Tim C
gt;gt;
gt;gt;
gt;gt;--
gt;gt;Debra Dalgleish
gt;gt;Excel FAQ, Tips amp; Book List
gt;gt;www.contextures.com/tiptech.html
gt;gt;
gt;
gt;
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.html