Need a special kind of "sort"...

R

RASelkirk

Hello,

I have from 60 to 150 cells all containing mixed 3-digit alpha-numeri
characters that need to be sorted in a way that is foreign to Excel
These cells are in one column that the user enters these codes into.
:eek:

For example, this is what I need:


- AC3
- AU9
- A31
- A90
- BX1
- BY4
- B24
- 1SC
- 19U
- 82C
- 829
- 95J


And I get this with the standard Excel sort (numbers 1st):


- 829
- 19U
- 1SC
- 82C
- 95J
- A31
- A90
- AC3
- AU9
- B24
- BX1
- BY4

I've built a VBA routine to move the cells that start with numerals t
the back of the list, but there is still a problem with the secon
"digit" being out of order as seen here after my VBA sort.

- A31
- A90
- AC3
- AU9
- B24
- BX1
- BY4
- 829
- 19U
- 1SC
- 82C
- 95J


I'm out of brainpower! There are maybe 600 total codes and I've though
about building a hash table, but surely there's a way to sort thes
cells as required?

Thanks!

Rus
 
M

merlin

RASelkirk said:
Hello,

I have from 60 to 150 cells all containing mixed 3-digit alpha-numeric
characters that need to be sorted in a way that is foreign to Excel.
These cells are in one column that the user enters these codes into.
:eek:

For example, this is what I need:


- AC3
- AU9
- A31
- A90
- BX1
- BY4
- B24
- 1SC
- 19U
- 82C
- 829
- 95J


And I get this with the standard Excel sort (numbers 1st):


- 829
- 19U
- 1SC
- 82C
- 95J
- A31
- A90
- AC3
- AU9
- B24
- BX1
- BY4

I've built a VBA routine to move the cells that start with numerals to
the back of the list, but there is still a problem with the second
"digit" being out of order as seen here after my VBA sort.

- A31
- A90
- AC3
- AU9
- B24
- BX1
- BY4
- 829
- 19U
- 1SC
- 82C
- 95J


I'm out of brainpower! There are maybe 600 total codes and I've thought
about building a hash table, but surely there's a way to sort these
cells as required?

Thanks!

Russ
Click to expand...

Not sure, but should the output be like this?AC3
AU9
A31
A90
BX1
BY4
B24
1SC
19U
82C
829
95J


If so, this is the same method I once used to sort IP-addresses.
Extract all three characters from the code, using the MID-function, so that
you will have three 1-character columns. Add the characters again using & in
the 4th column.
Finally, copy and paste special their values.
So if Cell A1 had the code AC3, then in B1, use =MID(A1;1;1)
in C1, use =MID(A1;2;1), in D1 use =MID(A1;3;1) and in E1 use =B1&C1&D1
 
R

Ron Rosenfeld

Hello,

I have from 60 to 150 cells all containing mixed 3-digit alpha-numeric
characters that need to be sorted in a way that is foreign to Excel.
These cells are in one column that the user enters these codes into.
:eek:

For example, this is what I need:


- AC3
- AU9
- A31
- A90
- BX1
- BY4
- B24
- 1SC
- 19U
- 82C
- 829
- 95J


And I get this with the standard Excel sort (numbers 1st):


- 829
- 19U
- 1SC
- 82C
- 95J
- A31
- A90
- AC3
- AU9
- B24
- BX1
- BY4

I've built a VBA routine to move the cells that start with numerals to
the back of the list, but there is still a problem with the second
"digit" being out of order as seen here after my VBA sort.

- A31
- A90
- AC3
- AU9
- B24
- BX1
- BY4
- 829
- 19U
- 1SC
- 82C
- 95J


I'm out of brainpower! There are maybe 600 total codes and I've thought
about building a hash table, but surely there's a way to sort these
cells as required?

Thanks!

Russ
Click to expand...

The following assumes your codes are all three characters long.

Set up a "helper column" with the following formula:

=(CODE(LEFT(A1,1))+100*(LEFT(A1,1)<"A"))*10^6+
(CODE(MID(A1,2,1))+100*(MID(A1,2,1)<"A"))*10^3+
CODE(RIGHT(A1,1))+100*(RIGHT(A1,1)<"A")

Then, sort ascending on the helper column.

Obviously, this could also be done as a VBA routine if you preferred.


--ron
 
Top