Alphanumeric sorting

**mj_bowen** · 10-16-2011, 10:03 AM

Hi,

I am trying to sort data using a column that contains alphanumeric data.

Is there a way to do this?

I have attached an example of how far I have got...

Any help would be appreciated,

Matt

http://www.box.net/shared/3jidahixs7ic9eslqyvi

**Andrew-R** · 10-16-2011, 10:37 AM

Can I suggest that you add a look-up table somewhere in your workbook with the possible values that could appear in your level column and the order you want them to appear in, you can then use a VLOOKUP to put these values next to the existing column and sort on that.

As the order of your levels seems to have no coherent pattern I don't see any other way you can do this.

**mj_bowen** · 10-16-2011, 12:13 PM

Originally Posted by Andrew-R

Can I suggest that you add a look-up table somewhere in your workbook with the possible values that could appear in your level column and the order you want them to appear in, you can then use a VLOOKUP to put these values next to the existing column and sort on that.

As the order of your levels seems to have no coherent pattern I don't see any other way you can do this.

Hi,
Thank you for your reply...I have added values for VLookUP to use but am not sure about how to use it!
Any help would be welcomed!

http://www.box.net/shared/ptju5fxicaq8j0uexm6u

**Andrew-R** · 10-16-2011, 12:20 PM

The VLOOKUP formula is in the attachment to my previous post, basically the format is =VLOOKUP(value to be found, range to search, column number to return, FALSE) the final FALSE just says you want exact matches, not closest matches.

I've enclosed it in an IFERROR statement, so that if it doesn't find a match it returns a values 1 greater than the maximum number of specified values, so unmatched levels will be sorted to the end.

**mj_bowen** · 10-16-2011, 12:35 PM

Originally Posted by Andrew-R

The VLOOKUP formula is in the attachment to my previous post, basically the format is =VLOOKUP(value to be found, range to search, column number to return, FALSE) the final FALSE just says you want exact matches, not closest matches.

I've enclosed it in an IFERROR statement, so that if it doesn't find a match it returns a values 1 greater than the maximum number of specified values, so unmatched levels will be sorted to the end.

Hi Andrew,

Thank you very much, that does exactly what I was after.
All the best,
Matt