Sort pivot table by value in TWO columns (first 2015's values, then 2014's values)

We can possibly do this in VBA are you interested in such a solution ?
gowflow

Since I am already quite good at vba, I could easily provide a solution that copies the pivot table to another area for further sorting.  That is not a solution I am interested in for this question.

But, if you have a vba solution that uses some "hidden" pivot table functionality, I would be very interested.

For instance Pivot tables have tons of features and some of them might not be completely available on the ribbon.  Any vba solution that uses such a feature would be fine.

rberke

The VBA I am talking about is for sure not copying the data but sorting it like you want where it is. Is this agreeable ?
gowflow

Sure, I am interested in what you come up with.

Bob

gowflow:

Bravo !!! I thought it was impossible, but you did it  !!! It is sad that excel's natural interface does not make this easy, but I have fixed your vba routine to do it..

It has been a VERY long time since I was forced to use a radix sort,  (For people unfamiliar with the term,  a radix sort starts with the least important field first, then continues through all fields ending with the most important field.  In the 1950's it was used by punch card sorting machines, so it has a long history of valuable uses.


I also noticed that your "manual" solution used 5 sort command when you only needed these 3.

 2014 desc click on cell J27 press on Sort in Data menu and choose Largest to Smallest OK
 2014 desc click on cell K27 press on Sort in Data menu and choose Largest to Smallest OK
 2015 desc click on cell L27 press on Sort in Data menu and choose Largest to Smallest OK

Here is the modified vba routine which works.

Sub SortPivot()
Dim strSortFields, aryFields, tbl As Range, piv As Object, i As Long
Dim order As Long
    strSortFields = "-l;-k;-j"  '  + means Ascending;  - means descending;  if ommitted, Ascending is assumed.
    aryFields = Split(strSortFields, ";")

    Set tbl = ActiveSheet.PivotTables(1).TableRange1
    Set tbl = tbl.Offset(2, 0).Resize(tbl.Rows.Count - 2)
    
    ' radix sort starts with least important sort field.
    For i = UBound(aryFields) To LBound(aryFields) Step -1
        order = xlAscending
        Select Case Left(aryFields(i), 1)
            Case "-": order = xlDescending: aryFields(i) = Mid(aryFields(i), 2)
            Case "+":  aryFields(i) = Mid(aryFields(i), 2)
        End Select
        tbl.Sort Key1:=Intersect(tbl, Columns(aryFields(i))), Order1:=order, Type _
        :=xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
    Next i

End Sub

Select allOpen in new window

congrats to gowflow  -- excellent job.

Well you totally surprised me with your very nice and professional way of taking back this routine. The Bravo goes to you well done.

As far as manual you are correct I just realized it a while under the shower that we only need to go backward from 2013 to 2015 and this should do it.

Well done.
gowflow

In the example I gave, "Part" is the ONLY  row field.

If there are more fields, things become more complicated.  For instance, let's say there were three row fields, Part, Discount, and PartDescription , and we manually use the radix approach to sort on columns x then y then z.

The table becomes sorted by "Part, Description, z, y, x"

In other words, Excel "locks" the sort order of all but the last row field.

Also, my macro completely fails for that situation. I could probably fix it to duplicate the manual process, but I would need a deeper understanding of the PivotTable object's structure.  Right now I don't need that flexibility, so I am leaving well enough alone.

Well
Actually I had 'fixed' the macro that you had fixed on the macro I posted !!!! :)

and I introduced 2 new columns values for Order number and Part description

Try running my macro and see if this is what is needed.
gowflow
ee-pivot-actual-V01.xlsm

I am sorry to take up you time with a problem that is already closed, but I find this kind of interesting.

Yes, your macro works well but when we do the 3 "radix sorts" using your macro, we get something totally different than when we do the 3 radix sorts manually  !!!!  

When we run your macro, the table becomes sorted by  " -i -j -k"  (which is a actually what I wanted.)
If we do it manually, the table becomes sorted by "Part, Description, -I, -j, -k" (which might be useful in the future.)

In other words, the macro accomplishes something that CANNOT be done manually.  Or, perhaps I just don't know how to do it manually?  

and,  another interesting question.  What if in the future, i wanted my macro to sort by "Part, Description, -I, -j, -k" ???  

Bob

By the way, it turns out that your macro and my macro do exactly the same thing, Mine also worked, I had just been testing it too quickly.  And, your macro has a very tiny bug,


one line of your macro should be changed to say sCol = Right(vCol(i), 1)

one line of your macro should be changed to say sCol = Right(vCol(i), 1)

Don't agree as if your table is in Col AA ... AF then sCol = Right(vCol(i), 1) will not get you the correct Col. In my macro you need to show the sign you cannot escape it like you pretend in yours that no sign = + sign.

Anyway if you are interested to dig further please be my guest and poast a link in here of a new question and will be glad to assist.

gowflow

I now realize that you key names MUST have a + or - in column 1, so there is no bug.  

And I think we have beat this topic to death, so I will refrain from further posts.  Thanks again for your help.

Bob

ok fine I will stop monitoring this question if you need my help on any issue pls msg me.
gowflow