Can't ungroup in Pivot Table Results - or how do I get what i want

[Dave]

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]

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]

That will do it!

Thanks for the idea

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.

 

[Debra Dalgleish]

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.