I work in the Auto Industry where I have to create a Pivot Table based on Auto information each month. The Sort Order of the Pivot Table needs to be
Model Series Door Cylinder Model ID Code Trim Level Transmission Color
This is the order of sort if the data is sorted through Excel because it allows for all Accord 2 Door, 4 Cylinder to be followed by 4 Door 4 Cylinder. I also use the Model ID code to make sure the Trim levels sort properly since the Trim level itself doesn't sort properly based on their names but I don't want the Model ID Code to show or stay in the Pivot Table. The Pivot Table tries to keep all 2 Door Accords together whether they are 2DR 4CYL or 2DR 6CYL but I need to Cylinders to stay grouped.
Then I can't seem to sort by Model ID code without it actually showing in my Pivot table. My end result should be something like the below example but Automatically sorted by the Pivot and not manually by me.
Accord 2 4 LX-S CVT Accord 2 4 EX CVT Accord 4 4 LX CVT Accord 2 6 EXLV6 AT6 Accord 4 6 EXLV6N AT6
The data used to sort the Trim level terms are called Model ID codes that sort nicely in Excel, for instance in the Accord 2DR 4Cyl:
CT1B3GEW = LX-S CT1B7GJW = EX
These Trims would not naturally sort in this manner since their letters might push them down. This codes needs to be used to sort properly but not viewable in the Pivot Table.
I have been sorting in Excel and then manually removing the column and moving things around to make the table appear in the correct order. Any help with this is GREATLY appreciated :-)