Pivot Table - Arbitrary Sort

Adrae

Active Member
Joined
Feb 19, 2002
Messages
306
Is there any way to add a secondary rank that would order the values within a group without creating more groups and subtotals?

Thanks :)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
On 2002-02-20 06:10, Adrae wrote:
Is there any way to add a secondary rank that would order the values within a group without creating more groups and subtotals?

Thanks :)

Care to provide a simple example using less than 10 data list records? Please specify your desired result.
 
Upvote 0
I did an example in excel, but don't know how to attach it. Anyway, let me see if I can explain:

Here's the sample source data:
rank order Item Amount
1 2 Example 1 100.00
1 1 Example 2 100.00
1 4 Example 3 100.00
1 3 Example 4 100.00
1 5 Example 5 100.00
2 5 Example 6 100.00
2 4 Example 7 100.00
2 1 Example 8 100.00
2 2 Example 9 100.00
2 3 Example 10 100.00

Here's how I want it to appear:
Total
1 Example 2 100
Example 1 100
Example 4 100
Example 3 100
Example 5 100
1 Total 500
2 Example 8 100
Example 9 100
Example 10 100
Example 7 100
Example 6 100
2 Total 500
Grand Total 1000

I wasn't able to include all the borders to make it look more like an actual pivot table, but you get the idea. Notice how it sorts the individual examples within each group (1 & 2) according to the order but without adding subtotals. Can this be done?
Thanks :)
 
Upvote 0
On 2002-02-20 07:16, Adrae wrote:
I did an example in excel, but don't know how to attach it. Anyway, let me see if I can explain:

Here's the sample source data:
rank order Item Amount
1 2 Example 1 100.00
1 1 Example 2 100.00
1 4 Example 3 100.00
1 3 Example 4 100.00
1 5 Example 5 100.00
2 5 Example 6 100.00
2 4 Example 7 100.00
2 1 Example 8 100.00
2 2 Example 9 100.00
2 3 Example 10 100.00

Here's how I want it to appear:
Total
1 Example 2 100
Example 1 100
Example 4 100
Example 3 100
Example 5 100
1 Total 500
2 Example 8 100
Example 9 100
Example 10 100
Example 7 100
Example 6 100
2 Total 500
Grand Total 1000

I wasn't able to include all the borders to make it look more like an actual pivot table, but you get the idea. Notice how it sorts the individual examples within each group (1 & 2) according to the order but without adding subtotals. Can this be done?
Thanks :)

Make your ROW area fields: 'rank', 'order',
'Item' (in this order). Double-click the
'order' field button and set its Subtotals
to "None". If desired hide the column
containing the 'order' field.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
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