alphanumeric sort

[wayneted]

I am trying to sort a series of financial futures contract according to alphanumeric codes, whereby M = June, N= July, Q= August....etc such that data appear like:

June 2013-July 2013
June 2013-November 2013
June 2013-January 2014
.
.
.
.
etc

The current data is in disarray whereby M3 is June 2013, F5 is January 2015, I want to be able to sort the following data:

M3-F5
M3-H4
M3-H5
M3-N3
M3-X3
N3-F4
N3-F5
N3-H4
N3-H5
N3-Q3
N3-U3

into this format:

M3-N3
M3-X3
M3-F4
M3-H4
M3-F5
M3-H5
N3-Q3
N3-U3
N3-F4
N3-H4
N3-F5
N3-H5

whereby all the June 2013 contracts are sorted first in sequential months, then the next series of July 2013 months begins and so on.

I would appreciate any guidance on sorting this complicated data set.

[oeldere]

Use Vlookup to dertermine your first month.

After that sort on that vaule.

You get bettter help if you add an excel file, without confidentional information.

Please also add the desired result.

[wayneted]

oeldere,

Thanks for the reply, I attached an Excel spreadsheet with the current format, sorted using Excel's built-in A to Z sort, and the desired sort outcome. I was thinking if inserting a helper column would help sort this out. Appreciate your input.

[oeldere]

I think your example is inconsistant (see the red tekst).

See if this is what you want.

I used helpcolumns and then sort (first on number) and second on letter.

[wayneted]

oeldere,

I did not mention the futures pairs are to be sorted from left to right, so my desired result is correct if arranged in that way.

I modified your help column to use MID function and then sorting the left hand side pairs first and did achieved the desired result.

To another question, there are additional single series which I would like arrange above the pairs sequentially as in attached file, they are currently sitting in the middle and end of the pairs when sorted, what further steps are needed to achieve this goal? Thanks.

[wayneted]

Alright I just found a solution by adding another helpcolumn using LEN function to sort the string from smallest to largest first. If anyone has a more elegant solution I'd be pleased to check it out, cheers.

[newdoverman]

This uses several helper columns so that each element of the cell contents can be sorted separately. The format of the file is for Excel 2010 which should be compatible with the version that you have in your profile.