Pivot Table: Two-column sort ?

I need a multiple column sort for a pivot table:

Count of Trade . Gender
Trade .......... Male ... Female ...Grand Total
Ag. Labourer ..... 57 ...................... 57
Female Servant .............. 39 ........... 39
Male Servant ..... 37 ...................... 37
Farmer ........... 29 ........ 1 ........... 30
[etc]

The required sort order is Grand Total (large>small) then Trade (A>Z), which seems to suggest a calculated field concatenating the Grand Total & Trade values, but I have had no success so far (and despite buying the Excel 2016 Bible !)

Any suggestions would be much appreciated.

I think this is about lack of understanding how sort works.

If the first sort criterion is the number in the Grand Total column, then the data is sorted by Grand Total.

Only if there are several rows where the Grand Total is the same, only then, can you apply a secondary sort order that then sorts all the duplicates total rows by the Trade. But if all the grand total values are different, the secondary sort order will never apply. The data is sorted by Grand total.

Hi Dave and welcome to the forum,

I always agree with Teylyn, but you might understand more about sorting by reading:

http://www.contextures.com/excel-piv...e-sorting.html

Originally Posted by teylyn

I think this is about lack of understanding how sort works... But if all the grand total values are different, the secondary sort order will never apply. The data is sorted by Grand total.

Many thanks - the first premise is correct ! Also, the full pivot table does contain several rows which have the same grand total value, hence the requirement for a secondary sort.

I had previously perused Contexture's 'Excel Pivot Table Sorting' page (thanks MarvinP), but still can't see an example which readily assists.

Dave.

PS I am struck by the ease with which Excel can handle primary/secondary sorting for simple (non-pivot) tables, in contrast to the same requirement with a pivot table, which seems markedly more difficult, esp. with a grand total column (vs FoxPro/DBASE, using INDEX).

Hi,

I don't believe you can have an automatic sort on two columns- you need to sort the row field alphabetically first, then sort by the data field, which should leave the row field in alphabetical order where possible. However, you would need code to reset this after refreshing the pivot if you do not wish to do so manually.

Can you sort the data source first? It may make a difference.

Originally Posted by xlnitwit

Can you sort the data source first ...

Yes - but the source sheet is protected and displayed in a sequence which needs to be retained (for checking against the hard-copy source), so preferably not.

Having just run a quick test with 4 rows of data, all with equal value, applying a sort by the value field resulted in a row order that was neither A-Z nor the order of the original data source. Hence, I suspect you might have to use code to analyse the data by value and alphabet and create an ordered listing, then clear the sort from the row field and apply a manual order based on the ordered listing.