weird Alphanumeric sorting issue, need to fix in VBA, other searches no help

9tanstaafl9 · May 7, 2013

Hi. I have single column of text that looks mostly like numbers, and I need it to sort (using VBA) in a more logical looking way. For example I would like it to look something like this:

1
2
2.3
2.3a
3
100
100-4321
--------------------
Not 1, 100, 2…
----------------------

I NEED TO FIX THIS USING VBA IF AT ALL POSSIBLE – This data is originally gathered by a query on another sheet (and comes in the order I’d like I might add). The problem is that I then copy with my macro onto the main sheet, and a that point it needs sorted with the old data that was there the week before. That’s when the sort looks weird.

Unfortunately I’m dealing with internal company codes that can’t be changed.

Is this a doable thing or not? If you say no I can tell them too bad and they have to deal with the ugly sort, but I don’t want to make that claim and then look like an idiot. More of an idiot – I don’t deal well with people. J

Thanks in advance for any help!

Using Excel 2010.

Sorry if this was already covered, but the search feature on MrExcel.com keeps bombing on me and I’m not finding what I need through Google.

BiocideJ · May 7, 2013

It is 'doable', however, it is a more complex problem than it should be. When you sort this data, do you expect other cells around those cells to be reordered as well or is it strictly a sort this range and only values within that given range will change positions?

oldbrewer · May 7, 2013

how many company codes - if it is around 100 you could create a custom sort order - a little example would be 1 2 3 5 4

then 5 4 3 2 1 sorted in ascending custom sort order would be 1 2 3 5 4

Rick Rothstein · May 7, 2013

Your example set is not "robust" enough for us to know your complete sort rules. First off, do all your values always start with a number? If so, please show us the order for the following set (which I am not sure is completely robust, but it will get us started)...

1
2
3
22
33
200
300
2a
22abc
200xy
3d
33ef
300xyz
2-1234
20-567
200-89
3-
30-98
300-123

If your numbers do not always start with a number, then throw some of them into the above examples as well.

9tanstaafl9 · May 7, 2013

BiocideJ said:
It is 'doable', however, it is a more complex problem than it should be. When you sort this data, do you expect other cells around those cells to be reordered as well or is it strictly a sort this range and only values within that given range will change positions?

In answer to Biocide, the latter, I need to sort the entire row based on this number. Sorry I should have mentioned that, but since it's always the way I sort I didn't think of it. If it matters there will be no blanks in the sorting column, but other columns could have empty cells.

In answer to OldBrewer, there could be thousands of codes, it varies by job.

In answer to Rick, I don't have any hard and fast rules, it just needs to APPEAR to make sense. So 1000 can't come before 2. I don't really care if 2-a, 2-b, and 2-c come before or after 2.1 - but they should definitely come before 3. As to the actual codes, these can be made changed any day and I have no control. They may NOT always start with a number, but currently they do (though I believe it is formatted as text on the import). CURRENTLY I only have codes that look like real numbers, though they are sorting out of order:


100
102
103
105
16
17
19
2000
240
250
280
310
311
350
410
420
450
460
510
520
521
535
54
540

<colgroup><col style="mso-width-source:userset;mso-width-alt:5778;width:119pt" width="158"> </colgroup><tbody>
[TD="width: 158"]10[/TD]

</tbody>

The issue is that I can't restrict them to numeric entries only, and it still needs to work.

AT THE VERY LEAST, even if the ones with dashes or alpha entries don't sort appropriately, I need to make it so that it doesn't go 1, 100, 2, 200... It could go 1, 2, 100, 200, 1a, 1-a or something like that. But 1, 1a, 1-a, 2, 100, 200 would be preferable. Or even 1a, 1-a, 1, 2, 100, 200. It just needs to have the fully numeric looking entries grouped appropriately.

Thanks for the quick replies. I didn't see them, I had a hard time refreshing for some reason.

Jennifer

Rick Rothstein · May 7, 2013

9tanstaafl9 said:
In answer to Rick, I don't have any hard and fast rules, it just needs to APPEAR to make sense. So 1000 can't come before 2. I don't really care if 2-a, 2-b, and 2-c come before or after 2.1 - but they should definitely come before 3. As to the actual codes, these can be made changed any day and I have no control. They may NOT always start with a number, but currently they do (though I believe it is formatted as text on the import). CURRENTLY I only have codes that look like real numbers, though they are sorting out of order:

I would still like you to show me the sorted list (the way you think they it should look) for the examples I gave you (but throw a few in that start with letters as you say that might be a possible future need). Your current situation of text numbers being only digits is simple to solve, but it would be better to build in the handling of non-all-digits now rather than trying to patch the code some time in the future.

9tanstaafl9 · May 7, 2013

Rick, sorry I didn't understand before: Something like this would be good, though keep in mind if it's easier to go another way (like the ones starting with letters being AFTER the numbers, that is fine too.

1
2
2-1234
2.1
2.a
20-567
22
22abc
200
200-89
200xy
3
3-
3d
30-98
33
33ef
300
300-123
300xyz
Handling
Interest
Shipping

So basically numbers before letters for first characters, for those starting with numbers it should sort first by the part that looks like a number, then blanks, then dashes or other characters, then letters. That's what makes sense to me, but it isn't a requirement that it be that specific order. It just needs to be something I can explain to them.

Thank you! I have to be gone for about an hour so if I don't reply please don't give up. I have to feed my little girl some lunch.

Jennifer

9tanstaafl9 · May 7, 2013

I meant to say if it would be easier for the codes starting with letters to come before those starting with numbers that would be fine.

Handling
Interest
Shipping
1
2
2-1234
2.1
2.a
20-567
22

9tanstaafl9 · May 8, 2013

Rick Rothstein said:
I would still like you to show me the sorted list (the way you think they it should look) for the examples I gave you (but throw a few in that start with letters as you say that might be a possible future need). Your current situation of text numbers being only digits is simple to solve, but it would be better to build in the handling of non-all-digits now rather than trying to patch the code some time in the future.

I provided the sorted list, late I'm sorry, I didn't understand what you wanted before. Can you please still help me?

Rick Rothstein · May 8, 2013

9tanstaafl9 said:
I provided the sorted list, late I'm sorry, I didn't understand what you wanted before. Can you please still help me?

I am working on it (on and off) as time permits (keep in mind that the people answering questions here are all volunteers taking time out from their normal lives to participate here)... your sorting requirements are somewhat "problematic".

weird Alphanumeric sorting issue, need to fix in VBA, other searches no help

9tanstaafl9

Well-known Member

Excel Facts

BiocideJ

Well-known Member

oldbrewer

Well-known Member

Rick Rothstein

MrExcel MVP

9tanstaafl9

Well-known Member

Rick Rothstein

MrExcel MVP

9tanstaafl9

Well-known Member

9tanstaafl9

Well-known Member

9tanstaafl9

Well-known Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

weird Alphanumeric sorting issue, need to fix in VBA, other searches no help

Well-known Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock