Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't ungroup in Pivot Table Results - or how do I get what i want
I have an Excel Pivot Table that I want structured like:
State - City - Last Name - Product - Sum of Sales I want the end result to Group (sort by) State, City, sum of Sales descending. This report would show the top sales dollars by city, and list which products sold and by whom. A Last Name can show up more than once depending on the total sales of product. The challenge is that I keep getting a group by Last name which affects the sort order. End result that I want displayed Georgia Atlanta Sally Blue cars 10,000 Georgia Atlanta Bob Green cars 9,000 Georgia Atlanta Bob Blue cars 8,500 Georgia Atlanta Sally Red cars 8,000 Is there a solution to this? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't ungroup in Pivot Table Results - or how do I get what iwant
YOu could add a column to the source data, in which you combine the four
fields. For example: =C2 & "_" & D2 & "_" & E2 & "_" & F2 Add a column heading, e.g. Group, and copy the formula down to the last row of data. Refresh the pivot table, and replace the existing row fields with the new field. Then sort the Total column. Dave wrote: I have an Excel Pivot Table that I want structured like: State - City - Last Name - Product - Sum of Sales I want the end result to Group (sort by) State, City, sum of Sales descending. This report would show the top sales dollars by city, and list which products sold and by whom. A Last Name can show up more than once depending on the total sales of product. The challenge is that I keep getting a group by Last name which affects the sort order. End result that I want displayed Georgia Atlanta Sally Blue cars 10,000 Georgia Atlanta Bob Green cars 9,000 Georgia Atlanta Bob Blue cars 8,500 Georgia Atlanta Sally Red cars 8,000 Is there a solution to this? Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't ungroup in Pivot Table Results - or how do I get what i
That will do it!
Thanks for the idea :-) "Debra Dalgleish" wrote: YOu could add a column to the source data, in which you combine the four fields. For example: =C2 & "_" & D2 & "_" & E2 & "_" & F2 Add a column heading, e.g. Group, and copy the formula down to the last row of data. Refresh the pivot table, and replace the existing row fields with the new field. Then sort the Total column. Dave wrote: I have an Excel Pivot Table that I want structured like: State - City - Last Name - Product - Sum of Sales I want the end result to Group (sort by) State, City, sum of Sales descending. This report would show the top sales dollars by city, and list which products sold and by whom. A Last Name can show up more than once depending on the total sales of product. The challenge is that I keep getting a group by Last name which affects the sort order. End result that I want displayed Georgia Atlanta Sally Blue cars 10,000 Georgia Atlanta Bob Green cars 9,000 Georgia Atlanta Bob Blue cars 8,500 Georgia Atlanta Sally Red cars 8,000 Is there a solution to this? Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't ungroup in Pivot Table Results - or how do I get what i
You're welcome! It won't be as tidy as having the fields in separate
columns, but at least you can sort it in the way that you wanted. Dave wrote: That will do it! Thanks for the idea :-) "Debra Dalgleish" wrote: YOu could add a column to the source data, in which you combine the four fields. For example: =C2 & "_" & D2 & "_" & E2 & "_" & F2 Add a column heading, e.g. Group, and copy the formula down to the last row of data. Refresh the pivot table, and replace the existing row fields with the new field. Then sort the Total column. Dave wrote: I have an Excel Pivot Table that I want structured like: State - City - Last Name - Product - Sum of Sales I want the end result to Group (sort by) State, City, sum of Sales descending. This report would show the top sales dollars by city, and list which products sold and by whom. A Last Name can show up more than once depending on the total sales of product. The challenge is that I keep getting a group by Last name which affects the sort order. End result that I want displayed Georgia Atlanta Sally Blue cars 10,000 Georgia Atlanta Bob Green cars 9,000 Georgia Atlanta Bob Blue cars 8,500 Georgia Atlanta Sally Red cars 8,000 Is there a solution to this? Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - divide two results | Excel Worksheet Functions | |||
Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
Pivot table grouped results | Excel Worksheet Functions | |||
Pivot Table for survey results with set of possible answers in PT's Rows Field? | Excel Discussion (Misc queries) | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) |