Need help on sorting locations with specific zoning criteria

[ec4excel]

Dear EXCEL experts,

I need help to brain stomp on how can I do a sort for the locations on Column A in the attached workbook, based on 2 special criteria.

My location reads like this:

XX(Can ignore) - x(Can ignore) Zone Range 2 - x - xxx (zone range 1)

Sample: 04-1A-1-025

I had manually done a sample sorted result on column I, based on Zone Range 2, A and B, followed by Zone Range 1.

The sort needs to be sorted by 1) Zone Range A and B, 1) Zone Range 1

then 2) Zone Range B and C, 2) Zone Range 1

then 3) Zone Range C and D, 3) Zone Range 1 and so on so forth.

A million thanks.

[Kaper]

My first proposition was: in B1 formula

Please Login or Register  to view this content.

copied all the way down and used for sorting.

so first Zone Range A and B and Zone Range 1
then Zone Range C and D and Zone Range 1 or Zone Range A and B and Zone Range 2 or Zone Range C and D and Zone Range 1
As I thought it is yur idea from the description.


But then realized that what you've shown in manual result is:

Zone Range A and B with all Zone Range 1 ... nnn in ascending order,
Zone Range C and D with all Zone Range 1 ... nnn in ascending order,
...
for such sequence use in B1 and copy down:

Please Login or Register  to view this content.

and sort your data on based on this formula

[ec4excel]

Dear Kaper,

Thank you for your effort but the result is not what I need. I think I should post this into the vba group as I think I need to use vba to copy and paste the result into a new worksheet as other then column A, I need the data in column B, C, D to sort according to column A too.

I will mark this thread as solved.

Thank you.

[Kaper]

If you have your location data in column A and columns B:D with other data then write the formula (I expect the second one above fits your needs better) in column E (cell E1 and copy down) and sort range A:E on column E ascending.

[Bo_Ry]

Please try at B2

=INDEX(A:A,MOD(AGGREGATE(15,6,INT((CODE(MID($A$1:$A$629,5,1))-1)/2)*10^8+RIGHT($A$1:$A$629,3)*10^4+ROW($A$1:$A$629),ROWS(B$1:B1)),10^4))