VBA Sort by custom array with partial values

[wdrvx]

Hey Guys,

I've been trying to set up a custom sort and got completely clueless after multiple attempts. Basically, I need to sort data by 2 columns with partial values in both of them.

Column B specifies the country and may contain more than one of them, each separated by a single space. Column C contains distribution code using a specific naming convention. The data needs to be sorted first by column B in the following order:

USA
USA Canada
Canada
Mexico
Mexico Panama
Panama

And then by column C in the following order (with a strict requirement that WUS-AXXC is always on top while the rest are sorted alphabetically following first part of the code: WUS*, WCA* etc.)

WUS-AXXC
WUS-ADDX
WUC-FKXP
WUC-FYPA
WCA-IXCK
WME-ACDW
WME-BBXC
WMP-CUXC
WPA-AAKX
WPA-FYPA
WPA-PYRQ


Below is an image that might help you understand what I'm trying to achieve here.

I'm not that good at VBA and still wondering whether something like that is possible, i'll be grateful for any assistance on this.
Thanks in advance!

 

[RickXL]

Hi,

I think you will be able to do what you want with a Custom Sort List.

Clisk the Sort and Filter Icon in the Editing section of the Hime tab.
Then select Custom Sort.
Now click the dropdown under Order on the right hand side and select Custom List.
Now you can paste in your list of Distribution Codes in your desired order.
Now click Add and OK.

When you want to sort your list you can select both columns of data and use the Custom List you have just created to put the rows into order.


Regards,