View Full Version : pivot table - sort items with different order for each parent item
tjmtest
08-04-2017, 03:22 AM
My table looks like this
P1
C1
C2
C3
P2
C1
C2
C3
I want it to look like this
P1
C1
C3
C2
P2
C1
C2
C3
But if i change the position for P1 items it also changes for P2 and ends up like this
P1
C1
C3
C2
P2
C1
C3
C2
Is there a way to achieve this? I want to do the sorting in VBA but even manually i cannot achieve what i want.
Bob Phillips
08-04-2017, 04:04 AM
Why would you need to do that? Surely, analysis means having the data to analyse, the order is not a data attribute.
tjmtest
08-04-2017, 04:38 AM
Why would you need to do that? Surely, analysis means having the data to analyse, the order is not a data attribute.
I have many reports (done using many different tools) and i want to keep the same ordering in all the reports. That said the main reason is because my boss wants it like this.
Bob Phillips
08-04-2017, 05:12 AM
That said the main reason is because my boss wants it like this.
The worst reason, but the most understandable :D.
I can't see anything in pivots that would facilitate this, the only way I could think is to have a calculated column in the original data that holds an index that gives the order that you want (something like =IF(A2="P1",MATCH(B2,{"C1","C3","C2"},0),1)), although that could get out of hand if you have lots of different types.
Paul_Hossler
08-04-2017, 06:36 AM
A kludgy workaround might be to (assuming the boss just wants to read the reports)
1. Have a PT do the heavy lifting the way most items are wanted, and have a macro to ...
2. Copy the PT to new sheet as values
3. Re-arrange just the non-standard order lines
19979
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.