PDA

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