Pivot Table Question

cgmackenzie

New Member
Joined
Apr 1, 2015
Messages
35
Hi All,

I was able to sort my pivot table from the largest value to the smallest value (in the "Sum of Total Contract Value (Not to Exceed)" column) while also getting the "Supplier Cleansed" column to the left to roll up all the other values, from largest to smallest, underneath the supplier's largest value (rows 8-12 are a good example). However, I can't recreate this view, please see the attached picture and thanks for the help!

Sort Hack.png
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hiding the details has made it difficult to be sure but it looks like your are running in Compact Form and have 2 fields in the first column.
Assuming that is the case, try this:-
1) Sort 1
  • Select any item in column A with the minus sign on it right click.
  • Sort > Sort More options > Descending > Sum of Total Contract Value (Not to Exceed)
2) Sort 2
  • Select any item in column A that does NOT have the minus sign on it (detail row) right click.
  • Sort > Sort More options > Descending > pick which field you want to sort by within each group
 
Upvote 0
Hi Alex, thanks for the reply! The table is in tabular form and there is only one field in column 'A' so unfortunately I wasn't able to duplicate it with your solution. Please let me know if you have any other ideas.

Sort Hack 2.png
 
Upvote 0
You have a 2 tier sort but "within" the customer you can't have it sorted descending by "Sum of Total Contract Value (Not to Exceed)" and descending Sum of 2021 Annual spend at the same time since the order conflicts eg in your example you can't have the $ 33k 2nd based on 1 column (not to exceed) but last based on the other column (annual spend)

So step 1 is:
How do you want the customers sorted eg
1) Sort 1
  • Select any item in column A and right click.
  • Sort > Sort More options > Descending > EITHER value column Sum of Sum of Total Contract Value (Not to Exceed) OR Annual Spend
Step 2:
2) Sort 2
  • Select any item in column B and right click.
  • Sort > EITHER
    • Just select ascending or descending (sort just on the field itself) OR
    • Sort More options > Descending > EITHER value column Sum of Sum of Total Contract Value (Not to Exceed) OR Annual Spend
 
Upvote 0
Solution

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top