Sorting Alphanumeric data

[David G.]

I have a long list of part numbers that contain mixed alpha-numeric
characters in no restricted fashion. i.e.
179802
A143B1
A1
CH03M
A15
R3
125B

etc.

The column never sorts correctly. "A3" follows "A15" because Excel
sorts everything like text.

I've looked at a number of examples of how others have addressed this
issue, but inconsistent format of my numbers prohibits using any
built-in Excel function that I know.

I'd just like to confirm that the only way to sort this list properly
is to parse all of the characters into alpha only, and numeric only,
groups. Put the groups into successive columns then have Excel sort
the data by the multiple columns.

Parsing would look like this:
179802 --> 179802
A143B1--> A 143 B 1
A1--> A 1
CH03M--> CH 03 M
A15 --> A 15
R3 --> R 3
125B --> 125 B

I would appreciate any comments.
THANKS!
David G.

 

[GS]

Well, David, this is a prime example of one of the many problematic
nuances that arise when people use an unstructured part numbering
methodology. You're probably right in that the only way you're going to
be able to do a wks sort this list is to parse out the characters into
separate columns. You might be better off to dump the entire range of
data into an array and sort the array by whatever element represents
the column number the part numbers are listed in.

Since this is going to be a persistent issue with no structured part
number methodology in place, I suggest you find one and renumber
everything so you don't have to use hacks and/or other mickey mouse
workarounds to work with them.<g>

I don't mean to offend! I just happen to design structured part
numbering methodolgies for clients and so I'm well aware of the many
negatives related to not having such a methodology in place. I hope
you'll understand, then, if I don't condone what I see as a 'bad idea'!

 

[David G.]

Well, David, this is a prime example of one of the many problematic
nuances that arise when people use an unstructured part numbering
methodology. You're probably right in that the only way you're going to
be able to do a wks sort this list is to parse out the characters into
separate columns. You might be better off to dump the entire range of
data into an array and sort the array by whatever element represents
the column number the part numbers are listed in.

Since this is going to be a persistent issue with no structured part
number methodology in place, I suggest you find one and renumber
everything so you don't have to use hacks and/or other mickey mouse
workarounds to work with them.<g>

I don't mean to offend! I just happen to design structured part
numbering methodolgies for clients and so I'm well aware of the many
negatives related to not having such a methodology in place. I hope
you'll understand, then, if I don't condone what I see as a 'bad idea'!

Couldn't agree more. No offense. The problem actually belongs to a
close friend. He works for a large auto parts manufacturer/supplier
who has bought up other companies and incorporated their parts (and
numbering system) -like the Borg- into their system. Unlike the Borg,
the various numbering systems (and sometimes companies) are nor
completely integrated.

And get this, the Marketing department is in charge of designing new
part numbers. They use the part number to "advertise" new or updated
products. They'll append a letter here, prefix a number there....

Thanks for your thoughts. I just wanted to make sure I wasn't
redesigning any wheels.
THANKS!
David G.

 

[Jim Cone]

Some self promotion for my Special Sort Excel add-in follows.
An independent review of the program however, can be found here...
http://www.contextures.com/excel-sort-addin.html

The program has 30+ different ways to sort including sorting...
...using the first group of numbers only
...using the last group of numbers only
...using all characters but with numbers sorting in strict numerical sequence.
(3 is treated as 03 so it sorts before 15)
Also prefix sort, suffix sort, mid sort, decimal sort, table sort, color sorts and more.

3 week free trial can be downloaded from...
http://www.mediafire.com/PrimitiveSoftware
--
Jim Cone
Portland, Oregon USA




"David G." <[email protected]>
wrote in message
I have a long list of part numbers that contain mixed alpha-numeric
characters in no restricted fashion. i.e.
179802
A143B1
A1
CH03M
A15
R3
125B

etc.

The column never sorts correctly. "A3" follows "A15" because Excel
sorts everything like text.
I've looked at a number of examples of how others have addressed this
issue, but inconsistent format of my numbers prohibits using any
built-in Excel function that I know.
I'd just like to confirm that the only way to sort this list properly
is to parse all of the characters into alpha only, and numeric only,
groups. Put the groups into successive columns then have Excel sort
the data by the multiple columns.

Parsing would look like this:
179802 --> 179802
A143B1--> A 143 B 1
A1--> A 1
CH03M--> CH 03 M
A15 --> A 15
R3 --> R 3
125B --> 125 B

I would appreciate any comments.
THANKS!
David G.

 

[GS]

Some self promotion for my Special Sort Excel add-in follows.
An independent review of the program however, can be found here...
http://www.contextures.com/excel-sort-addin.html

The program has 30+ different ways to sort including sorting...
...using the first group of numbers only
...using the last group of numbers only
...using all characters but with numbers sorting in strict numerical
sequence.
(3 is treated as 03 so it sorts before 15)
Also prefix sort, suffix sort, mid sort, decimal sort, table sort, color
sorts and more

Jim,
That's an excellent addin! Haven't tried it but the review tells me
it's a great utility!

 

[Jim Cone]

Garry,
Thanks for your comments.
Debra Dalgleish became one of my favorite people. <g>
Her website, in my opinion, is one of the best Excel how to sites there is.
'---
FWIW, the Special Sort program also has an undo option for each sort.
However, writing that undo code took all the fun out of creating the program. <g>
Regards,
Jim Cone


"GS" <[email protected]>
wrote in message

 

[GS]

Jim Cone explained on 5/3/2011 :

FWIW, the Special Sort program also has an undo option for each sort.
However, writing that undo code took all the fun out of creating the program.
<g>

I can appreciate that! Writing undo code for VB[A} is a PITA to put it
nicely...

BTW, Debra's office was around the corner from me when I live in
Toronto. she's in Mississauga, a suburb of Toronto. I've learned a lot
from her website and I also agree with your assessment of being one of
the best 'how to' sites.