Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Alphanumeric data
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Alphanumeric data
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'! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Alphanumeric data
On Tue, 03 May 2011 14:01:45 -0400, GS wrote:
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Alphanumeric data
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." 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Alphanumeric data
on 5/3/2011, Jim Cone supposed :
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! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Alphanumeric data
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" wrote in message ... on 5/3/2011, Jim Cone supposed : 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! -- Garry |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Alphanumeric data
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting alphanumeric data at 2nd, 3rd or 4th character position | Excel Discussion (Misc queries) | |||
Sorting Alphanumeric data in Excel 2003 | Setting up and Configuration of Excel | |||
Sorting alphanumeric | Excel Discussion (Misc queries) | |||
sorting alphanumeric | Excel Discussion (Misc queries) | |||
alphanumeric sorting | Excel Discussion (Misc queries) |