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.

9tanstaafl9 · May 8, 2013

I'm so sorry, I didn't mean to rush you. I was worried that since I hadn't replied appropriately or timely that you might think I was some sort of jerk who you wouldn't want to help. I am always very grateful for the help I receive here.

Also, if my last post ended up here or in your mailbox several times that was unintentional. I had had the window open and the reply didn't look like it had posted even an hour later. It wasn't until I quit out of Firefox and came back in that I saw your response, to a post I thought hadn't posted. Now I'm really worried that a string of them will appear at any moment.

Also, the report "works" fine for the moment, the sorting isn't a dealbreaker, they can just deal with it. The numbers the accounting department needs are all there. Please don't lose any sleep. It was good enough for me to tell them that it was possible and that I could give them an update eventually. Don't lose sleep.

Regards,
Jennifer

Rick Rothstein · May 8, 2013

Give this macro a try (on a copy of your worksheet until you are sure it works for you) and let me know if the listed order is acceptable. Note... make sure to change the two Const statement (highlighted in red below) assignments to the row and column for the first data (not header) cell in the column with the values being sorted on.

Rich (BB code):

Sub SpecialSorter()
  Dim R As Long, X As Long, DataLastRow As Long, UnusedCol As Long, Index As Long
  Dim vIn As Variant, vOut As Variant, vFinal As Variant
  Const DataCol As String = "C"
  Const DataStartRow As Long = 2
  DataLastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
  vIn = Cells(DataStartRow, DataCol).Resize(DataLastRow - DataStartRow + 1)
  ReDim vOut(1 To UBound(vIn, 1), 1 To 2)
  UnusedCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  For R = 1 To UBound(vIn, 1)
    vIn(R, 1) = Replace(vIn(R, 1), "-", ".")
    If IsNumeric(vIn(R, 1)) Then
      vOut(R, 1) = Val(vIn(R, 1))
      vOut(R, 2) = ""
    Else
      For X = Len(vIn(R, 1)) To 1 Step -1
        If Mid(vIn(R, 1), X, 1) Like "#" Then Exit For
      Next
      vOut(R, 1) = Val(Left(vIn(R, 1), X))
      vOut(R, 2) = Mid(vIn(R, 1), X + 1)
    End If
  Next
  On Error GoTo Whoops
  Application.ScreenUpdating = False
  Cells(DataStartRow, UnusedCol).Resize(DataLastRow - DataStartRow + 1, 2).Cells = vOut
  Cells(DataStartRow, "A").Resize(DataLastRow - DataStartRow + 1, UnusedCol + 1).Sort _
          Key1:=Cells(DataStartRow, UnusedCol), Order1:=xlAscending, _
          Key2:=Cells(DataStartRow, UnusedCol + 1), Order2:=xlAscending, _
          Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
          xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
  ReDim vFinal(1 To UBound(vOut), 1 To 2)
  vOut = Cells(DataStartRow, UnusedCol).Resize(DataLastRow - DataStartRow + 1, 2)
  ReDim vFinal(1 To UBound(vOut), 1 To 2)
  Index = 1
  For X = 0 To 9
    For R = 1 To UBound(vOut, 1)
      If Int(Left(vOut(R, 1), 1)) = X And IsEmpty(vFinal(R, 1)) Then
        vFinal(R, 1) = Index
        Index = Index + 1
      End If
    Next
  Next
  Cells(DataStartRow, UnusedCol).Resize(DataLastRow - DataStartRow + 1, 2) = vFinal
  Cells(DataStartRow, "A").Resize(DataLastRow - DataStartRow + 1, UnusedCol).Sort _
          Key1:=Cells(DataStartRow, UnusedCol), Order1:=xlAscending, _
          Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
          xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
  Columns(UnusedCol).Resize(, 2).Delete
Whoops:
  Application.ScreenUpdating = True
End Sub

9tanstaafl9 · May 9, 2013

Rick,

Thank you SO much for the code. I would have replied earlier but have been having difficulty connecting to this site.

There was one small issue where it would not work at all, but that was because I have a notes section underneath that was screwing up where your code was finding the last row. Theoretically they may enter any number of rows of notes underneath, and worse there may be blank rows in between. But that isn't your problem or what I wrote you about, I can figure it out with some sort of offset from the either the word NOTES: (with the named range "NOTES") which always exactly four rows beneath where the start row should be. So for testing purposes for your macro I just deleted the bottom section.

Your macro appears to do ALMOST what I want but not quite. It groups properly within each group startning with the same character, but doesn't intermix them. All items starting with a 2 come before all items starting with a 3 for example. What I would like would be for 2000 to be after 930 not after 280.

<tbody>
[TD="class: xl66"]

<tbody>
[TD="class: xl67"][/TD]

[TD="class: xl68"]Handling[/TD]

[TD="class: xl68"]Shipping[/TD]

[TD="class: xl68"]10[/TD]

[TD="class: xl68"]16[/TD]

[TD="class: xl68"]17[/TD]

[TD="class: xl68"]19[/TD]

[TD="class: xl68"]103[/TD]

[TD="class: xl68"]105[/TD]

[TD="class: xl68"]240[/TD]

[TD="class: xl68"]250[/TD]

[TD="class: xl68"]280[/TD]

[TD="class: xl68"]2000[/TD]

[TD="class: xl68"]310[/TD]

[TD="class: xl68"]311[/TD]

[TD="class: xl68"]350[/TD]

[TD="class: xl68"]410[/TD]

[TD="class: xl68"]420[/TD]

[TD="class: xl68"]460[/TD]

[TD="class: xl68"]54[/TD]

[TD="class: xl68"]54.1[/TD]

[TD="class: xl68"]56[/TD]

[TD="class: xl68"]510[/TD]

[TD="class: xl68"]520[/TD]

[TD="class: xl68"]535[/TD]

[TD="class: xl68"]540[/TD]

[TD="class: xl68"]6-a[/TD]

[TD="class: xl68"]6[/TD]

[TD="class: xl68"]6.432[/TD]

[TD="class: xl68"]62[/TD]

[TD="class: xl68"]7[/TD]

[TD="class: xl68"]730[/TD]

[TD="class: xl68"]8a[/TD]

[TD="class: xl68"]86[/TD]

[TD="class: xl68"]87[/TD]

[TD="class: xl68"]800[/TD]

[TD="class: xl68"]811[/TD]

[TD="class: xl68"]820[/TD]

[TD="class: xl68"]820.456[/TD]

[TD="class: xl68"]840[/TD]

[TD="class: xl68"]90[/TD]

[TD="class: xl68"]930[/TD]

</tbody>

[/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"]Thanks again for your help![/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

</tbody>

Rick Rothstein · May 9, 2013

9tanstaafl9 said:
Your macro appears to do ALMOST what I want but not quite. It groups properly within each group startning with the same character, but doesn't intermix them. All items starting with a 2 come before all items starting with a 3 for example. What I would like would be for 2000 to be after 930 not after 280.

That is not the impression I got from your posting in Message #7. Let's try again.... show me the order you would like to see the following items in...

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

9tanstaafl9 · May 9, 2013

Ooh! One thing that may be affecting the sort. Sorry I didn't see this before but your code is so far above my level that I had kind of glazed over and didn't see the words "UnusedCol". How is that determined? My data for the COSTCODES we are sorting is in column C, but there is also data in Column B (not A), AND there is a blank column in the middle. The first truly unused column is col X.

I was trying to keep my original post simple, but I see now I should have mentioned this.

Also, Col B eventually needs to be figured into the sort. I had thought your code would be more straightforward and I could adapt it, but I'm not sure. now. My original sort looked like this:

Range(Range("TopDescription"), Range("TopDescription").End(xlDown)).EntireRow.Sort Key1:=Range("TopCostcode"), Order1:=xlAscending, Key2:=Range("TopPhase" _
), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal

Where TopCostcode is C16 and TopPhase is B16

9tanstaafl9 · May 9, 2013

I just saw your reply you wrote while I was typing. I will put int he correct order now and i"m sorry I mislead you. Also my comments about the column may be moot if you intended the sort to work that way.

Rick Rothstein · May 9, 2013

9tanstaafl9 said:
I will put int he correct order now and i"m sorry I mislead you.

If you have more than one column involved in the sorting operation, then show me sample data from both columns, then show me how that data should look after being sorted. Try to use a varied data sample similar to the variation I showed in Message #14, but for the two columns involved in determining the final sorted order.

9tanstaafl9 · May 9, 2013

Oh god, I see that I was an idiot and you did what I asked. My only explanation for my apparent lack of brainpower was that I was trying to hurry and was worried you would be angry because I took too long to reply, and I only focused on the weird numbers not the regular ones. I am SO sorry I wasted your time.

The real order would be as follows:

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

9tanstaafl9 · May 9, 2013

Just saw your last post, redoing my list now

9tanstaafl9 · May 9, 2013

That's a little difficult. Right now the first column, B, IS normal and only has numbers like 1, 2, 14, 15 (though they come in as text). The issue is that I need the ability for them to be anything. The original sort however would just currently be Excel's normal default sort order. The rules should be the same for Col B and ColC -- it should sort first by col B (phase) and then by Col C (cost code).

B C

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
[TD="class: xl68, width: 64"]Phase
1
[/TD]
[TD="class: xl68, width: 64"]CostCode
1
[/TD]

[TD="class: xl68"]1
[/TD]
[TD="class: xl68"]2[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]2-1234[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]2.1[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]2.a[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]3[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]3-[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]3d[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]3000[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]3000-987[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]3000xyz[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]Handling[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]Interest[/TD]

[TD="class: xl68"]1[/TD]
[TD="class: xl68"]Shipping[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]1[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]2.a[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]3[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]3-[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]3d[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]200[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]200-89[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]200xy[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]300[/TD]

[TD="class: xl68"]1-a[/TD]
[TD="class: xl68"]Shipping[/TD]

[TD="class: xl68"]2[/TD]
[TD="class: xl68"]300[/TD]

[TD="class: xl68"]2[/TD]
[TD="class: xl68"]300-123[/TD]

[TD="class: xl68"]2[/TD]
[TD="class: xl68"]300xyz[/TD]

[TD="class: xl68"]2[/TD]
[TD="class: xl68"]2000[/TD]

[TD="class: xl68"]2[/TD]
[TD="class: xl68"]3000-987[/TD]

[TD="class: xl68"]2[/TD]
[TD="class: xl68"]3000xyz[/TD]

[TD="class: xl68"]100b[/TD]
[TD="class: xl68"]2.1[/TD]

[TD="class: xl68"]100b[/TD]
[TD="class: xl68"]2.a[/TD]

[TD="class: xl68"]100b[/TD]
[TD="class: xl68"]3[/TD]

[TD="class: xl68"]100b[/TD]
[TD="class: xl68"]3-[/TD]

</tbody>

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

9tanstaafl9

Well-known Member

9tanstaafl9

Well-known Member

Excel Facts

Rick Rothstein

MrExcel MVP

9tanstaafl9

Well-known Member

Rick Rothstein

MrExcel MVP

9tanstaafl9

Well-known Member

9tanstaafl9

Well-known Member

Rick Rothstein

MrExcel MVP

9tanstaafl9

Well-known Member

9tanstaafl9

Well-known Member

9tanstaafl9

Well-known Member

Similar threads

Forum statistics

Share this page

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

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

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