Pivot table sort
Posted by Rob on October 30, 2001 9:35 AM
I have a pivot table with 3 fields on the left and one data field. (showing inventory information). The 3 fields on the left are width, thickness, and length. I want to sort by the data (total units) in descending order of the unique combination of width, thickness, and length. (ex - 72", 1/2", 240" - 100 units
60", 3/4", 120" - 98 units
96", 1/2", 240" = 95 units
I can do this sort on one field, but not the combination of all 3. Any ideas would be greatly appreciated.
Posted by marbel on October 30, 2001 12:07 PM
Without getting too involved within the pivot table, you could copy out the table as values, fill in the blanks (edit/goto/special/blanks, type =, up arrow, ctrl+enter) and then sort using the technique desribed here:
http://support.microsoft.com/support/Office/InProdHlp/Excel/dhowSort2Key.asp
There is likely a solution within the pivot table itself, but I'll bet it's going to be cumbersome. Try advanced on the field setting of your choice, base it on the proper data field, and good luck....
mb
Posted by Mark W. on October 30, 2001 1:43 PM
Too easy, just...
Double-click the 'Width' field button, press
[ Advanced... ], change the AutoSort options
to "Descending" and Using field "Sum of Units"
(or "Sum of Inventory"... whatever you named
your Data item).
Posted by Robin on October 30, 2001 2:49 PM
Re: Too easy, just...
Thanks, but as a I said, I can sort by one field. The task it to sort by each unique combination of the records in each of the three fields. See the example below. Thanks!
(ex - 72", 1/2", 240" - 100 units
Posted by Mark W. on October 31, 2001 5:29 AM
Re: Too easy, just...
I did see your example below... and I reproduced
the results. Did you try it?
