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

The attached spreadsheet produces a pivot table that looks like this.
ee-pivot-image.pngI want to sort it so that items with largest 2015 sales are first.

When a part has no sales in 2015, I want the sort to be based on 2014 sales.

I know I can simply copy the pivot table values to another location, but I wonder if there is a way to do it directly inside the pivot table.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

We can possibly do this in VBA are you interested in such a solution ?
rberkeConsultantAuthor Commented:
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.

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 ?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

hv been working on this for the past 2 days and searched a lot on the web and seem the trick is the following

BTW I started building a VBA routine to use exactly the fields that you posted Col 2015 descending then Col 2014 Descending then Col 2013 descending but seems that excel ignores these when it comes to Sorting. I am including anyway the code but simply ignore it in this case as it does not work but As I understand you are good in VBA so you can use some of this when you have none Pivot data.

To get back to our subject you need to do the following:
1) Select your data from the pivot table from Cell I27 to M32
2) on the Data Menu select the Sort button it will open a window with Manual selected press on 'More Options' and here is the trick: Unselect Sort Automatically every time the report is updated. This should be off all the time when you have multiple columns sorting. Press OK, OK
3) Now we need to sort the data the way you like it in your case it is 2015 desc/2014 desc/2013 desc but here is the trick: Pivot sort 1 column at a time so will do this sorting 2015,2014,2013 then as the data is correctly sorted within the rows will do 2013,2014,2015 and your data will be sorted as you want.

4) If 3 is not clear will do step by step:
2015 desc click on cell L27 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
2013 desc click on cell J27 press on Sort in Data menu and choose Largest to Smallest OK

Now we need to go back reverse
We already hv sorting 2013
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

and your data is sorted the way you want.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rberkeConsultantAuthor Commented:

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

Open in new window

rberkeConsultantAuthor Commented:
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.
rberkeConsultantAuthor Commented:
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.
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.
rberkeConsultantAuthor Commented:
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" ???  


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.

rberkeConsultantAuthor Commented:
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.

ok fine I will stop monitoring this question if you need my help on any issue pls msg me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.