Tough sort Problem

[jomili]

See the attached file. This is the result of a Pivot. MOS is Month of Service, MOP is Month of Payment. The pivot is created by another team, I can move fields around but don't have access to the raw data. My issue is that for Budget Ref 2014 MOP 092014 consistently populates out of order. I'm creating the pivot via a macro, but don't know how to get the 092014 to sort correctly. After the pivot is made, I convert to values, so I'm open to an "outside the pivot" method of sorting it correctly. Any help is greatly appreciated.

[TMS]

I do not understand what you mean by:

My issue is that for Budget Ref 2014 MOP 092014 consistently populates out of order.

Regards, TMS

[jomili]

Sorry, should have been more explanatory. In the MOP column, in Budget_Ref 2014, the value for 092014 comes after 052014 and before 062014, so is out of order. In Budget_Ref 2015, the 092015 value follows 082015 and precedes 102015, as it should. In my pivot, I'd like the 2014 values to follow 01, 02, 03 etc, but the pivot doesn't natively sort in that order for 2014, though it does for 2015. That's where I'm stuck.

[Pepe Le Mokko]

Without access to the raw data it's difficult to say

[jomili]

While I'm curious about the "why", I'm more concerned with developing a workaround. I tried using a custom list to sort, and couldn't get that to work, which makes me think that "092014" may have some special character associated with it.

Followup: A custom sort DOES work on the pivot, but isn't a viable option, because if I use a custom sort on that field I have to do it for all the values, so I'd need an "01" through "12" & YEAR for each year of 2014, 2015, 2016 and 2017. Thats 12*6*4 (288) characters, and a custom list can only handle 255 characters. So, I need to do it after the pivot is converted to values.

Alternative: I've discovered if a double-click my grand total to get all the values, and then run a pivot on those results, 092014 falls right in place. Pursuing that option now.

[TMS]

Select the cells from column E to column R relating to 2014 and just sort them ascending, no headers, and choose "sort anything that looks like a number, as a number".

See the attached updated example.

Regards, TMS

[jomili]

Okay, so how do I do that by VBA? Use something like "if right(Cell,4) = "2014" then Sort"?

[TMS]

Well, given the layout of your data, the first problem would be how to locate the data to be sorted. Once you have determined that it is in rows 7 to 31, you can then loop through each Cell in range E7:E31 and build a range of cells where Right(Cell,4) = "2014". You then resize that range to include columns F to R ... and then Sort that range.

I guess you could loop through column C to locate the 2014 sub-heading to get the start of each "potential" sort range and then End down to locate the end of it.

Regards, TMS

[jomili]

I'm coming up empty on how to select each range in column E. It's easy to find the first 2014, and it's easy to find the last 2014. What's hard is finding where the division are in between.

[nilem]

Hi jomili,
try this

Please Login or Register  to view this content.

[jomili]

Nilem,
Your code worked perfectly for my sample file. Now I've got to try to make it more versatile. For instance, not all of my files will have "B.1.11". It could one of about 50 different options, so I'll alter I to look for "STRATEGY". As well, for some of these there won't be 12 months of MOS data, but there definitely can't be more than 12, so I guess the offset of 14 will still work. I'll just have to make sure my pivots always start in B and not A.

Thanks so much for showing me how to do it!

[jomili]

Just realized I never showed my updated code. I didn't change Nilem's code much. Changes are shown in red.

Please Login or Register  to view this content.

Thanks so much Nilem; you really made a difference!