Sorting alpha numeric sequence
I am using Excel 2003. I have a series of alphanumeric codes that run down the page, for example from C1 to C11. When I sort them, they sort as follows
C1
C10
C11
C2
C3
C4
C5
C6
C7
C8
C9
How do I get them to sort numerically whilst retaining the 'C'.
C1
C10
C11
C2
C3
C4
C5
C6
C7
C8
C9
How do I get them to sort numerically whilst retaining the 'C'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Matt:
How about splitting the values into two arrays and sorting them simultaneously?
Jaes
How about splitting the values into two arrays and sorting them simultaneously?
Jaes
That isn't a bad idea, Jaes, but what if these alpha numeric values are column A and he has data in columns B:Z that he also needs sorted? Too many calls to .Cut/.Insert slows macros down, at least for me :( Besides, excel's sort is pretty fast, hard to beat it for multi-dimensions
Matt:
Very true. I use a version of the old BubbleSort for my arrays. You may have seen it in my posts. I wish I could tap into Excel's sort. Better yet, why doesn't Excel VBA come with a function like Perl's <=> or 'cmp' functions? Perhaps the new version will have a better way to sort arrays.
Jaes
Very true. I use a version of the old BubbleSort for my arrays. You may have seen it in my posts. I wish I could tap into Excel's sort. Better yet, why doesn't Excel VBA come with a function like Perl's <=> or 'cmp' functions? Perhaps the new version will have a better way to sort arrays.
Jaes
ASKER
Xanius, your option seems to work out fine. Many thanks
Jaes,
As far as I'm concerned, nothing beats the TriQuick sort routines at Philippe Lord made.. take a look at http://software.bvsystems.be/modules/array%20sorting.bas
Extremely fast, even for huge, complex (one-dimensional) arrays
Matt
As far as I'm concerned, nothing beats the TriQuick sort routines at Philippe Lord made.. take a look at http://software.bvsystems.be/modules/array%20sorting.bas
Extremely fast, even for huge, complex (one-dimensional) arrays
Matt
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Hi,
If you fancy a VBA-based solution, I have amended my code (previously posted in [ https://www.experts-exchange.com/questions/21601371/sorting.html ] ), to suit your requirements.
The code assumes your list starts in column A, row 1, and has one alphanumeric character prefixing the numeric characters - please advise if this is not the case.
Are you familiar with inserting VBA code in your workbooks?
Right-click any sheet within the appropriate workbook, and select "View Code" from the pop-menu displayed.
When the "Microsoft Visual Basic" environment is displayed, look for a window named "Project - VBAProject"; if it is not shown, depress & hold down the [CTRL] key, press & release the [R] key, then release the [CTRL] key.
Locate the entry in the "Project - VBAProject" window for "VBAProject (<name of your .xls file>)", and right-click it.
Select "Insert->" then "Module" from the menu displayed, and then paste the following into the code window (most probably "Module1", but may be "Module" followed by a number) that is then shown:
' Start of Code...
Option Explicit
Public Sub Sort_Column_A()
' --------------------------
' Experts Exchange Question:
' Home \ All Topics \ Applications \ MS Office \ Excel
' https://www.experts-exchange.com/questions/21711174/Sorting-alpha-numeric-sequence.html
' Sorting alpha numeric sequence
'
' See also:
' https://www.experts-exchange.com/questions/21601371/sorting.html
' Sorting
'
' Copyright (c) 2005-2006 Clearlogic Concepts (UK) Limited
' N.Lee [ http://NigelLee.info ] - 20 October 2005 & 26 January 2006
' --------------------------
Dim objCell As Range
Dim strArray() As String
Dim vntSplit As Variant
On Error Resume Next
For Each objCell In Intersect([A1:A65536], ActiveSheet.UsedRange)
ReDim Preserve strArray(objCell.Row) As String
strArray(objCell.Row) = Left$(objCell, 1) & vbTab & Format$(Mid$(objCell, 2), "000000")
Next objCell
If (blnQuick_Sort_Strings(str
For Each objCell In Intersect([A1:A65536], ActiveSheet.UsedRange)
vntSplit = Split(strArray(objCell.Row
objCell = vntSplit(0) & Val(vntSplit(1))
Next objCell
End If
End Sub
Private Function blnQuick_Sort_Strings(ByRe
Optional ByRef lngLow_Value As Long = -1&, _
Optional ByRef lngHigh_Value As Long = -1&, _
Optional ByVal blnAlpha_Sort As Boolean = True) As Boolean
' --------------------------
' Experts Exchange Question:
' Home \ All Topics \ Applications \ MS Office \ Excel
' https://www.experts-exchange.com/questions/21601371/sorting.html
' Sorting
'
' Copyright (c) 2005 Clearlogic Concepts (UK) Limited
' N.Lee [ http://NigelLee.info ] - 20 October 2005
' --------------------------
Dim blnReturn As Boolean
Dim blnSwap As Boolean
Dim lngLow As Long
Dim lngHigh As Long
Dim lngPivot As Long
Dim lngPosLow As Long
Dim lngPosHigh As Long
Dim strPivot As Variant
On Error GoTo Err_blnQuick_Sort_Strings
blnReturn = False
lngLow = IIf(lngLow_Value > -1&, lngLow_Value, LBound(strArray))
lngHigh = IIf(lngHigh_Value > -1&, lngHigh_Value, UBound(strArray))
If lngLow >= lngHigh Then
blnQuick_Sort_Strings = True
Exit Function
End If
' If only 2 elements in this subdivision; swap them if out of order...
If (lngHigh - lngLow) = 1& Then
If (blnAlpha_Sort) Then
blnSwap = (strArray(lngLow) > strArray(lngHigh))
Else
blnSwap = (Val(strArray(lngLow)) > Val(strArray(lngHigh)))
End If
If (blnSwap) Then
Call strSwap(strArray(lngLow), strArray(lngHigh))
End If
blnQuick_Sort_Strings = True
Exit Function
End If
' Pick a pivot element at random & move it to the end...
lngPivot = CLng(Int(Rnd(1) * (lngHigh - lngLow) + 1&) + lngLow)
Call strSwap(strArray(lngHigh),
strPivot = UCase$(strArray(lngHigh))
Do
lngPosLow = lngLow
lngPosHigh = lngHigh
' Move in from both sides towards the pivot element...
If (blnAlpha_Sort) Then
Do While (lngPosLow < lngPosHigh) And (UCase$(strArray(lngPosLow
lngPosLow = lngPosLow + 1&
Loop
Do While (lngPosHigh > lngPosLow) And (UCase$(strArray(lngPosHig
lngPosHigh = lngPosHigh - 1&
Loop
Else
Do While (lngPosLow < lngPosHigh) And (Val(strArray(lngPosLow)) <= Val(strPivot))
lngPosLow = lngPosLow + 1&
Loop
Do While (lngPosHigh > lngPosLow) And (Val(strArray(lngPosHigh))
lngPosHigh = lngPosHigh - 1&
Loop
End If
' If we haven't reached the pivot element then two elements on either side are out of order & need swapping...
If lngPosLow < lngPosHigh Then
Call strSwap(strArray(lngPosLow
End If
Loop While (lngPosLow < lngPosHigh)
' Move the pivot element back to its proper place in the array...
Call strSwap(strArray(lngPosLow
' Recursively call the Sort procedure (pass the smaller subdivision first to use less stack space)...
blnReturn = True
If (lngPosLow - lngLow) < (lngHigh - lngPosLow) Then
blnReturn = blnQuick_Sort_Strings(strA
If (blnReturn) Then
blnReturn = blnQuick_Sort_Strings(strA
End If
Else
blnReturn = blnQuick_Sort_Strings(strA
If (blnReturn) Then
blnReturn = blnQuick_Sort_Strings(strA
End If
End If
Exit_blnQuick_Sort_Strings
On Error Resume Next
blnQuick_Sort_Strings = blnReturn
Exit Function
Err_blnQuick_Sort_Strings:
blnReturn = False
Resume Exit_blnQuick_Sort_Strings
End Function
Private Sub strSwap(ByRef strFirst As String, _
ByRef strSecond As String)
' --------------------------
' Experts Exchange Question:
' Home \ All Topics \ Applications \ MS Office \ Excel
' https://www.experts-exchange.com/questions/21601371/sorting.html
' Sorting
'
' Copyright (c) 2005 Clearlogic Concepts (UK) Limited
' N.Lee [ http://NigelLee.info ] - 20 October 2005
' --------------------------
Dim strTemp As String
On Error Resume Next
strTemp = strSecond
strSecond = strFirst
strFirst = strTemp
End Sub
' ...End of Code
Now depress & hold the [ALT] key, press & release [F11], then release the [ALT] key.
Save your workbook (to make sure the code is saved).
When you wish to run the code, hold the [ALT] key, press & release [F8], then release the [ALT] key.
Select "Sort_Column_A" from the list displayed in the "Macro" dialog window, and click [Run].
BFN,
fp.
If you fancy a VBA-based solution, I have amended my code (previously posted in [ https://www.experts-exchange.com/questions/21601371/sorting.html ] ), to suit your requirements.
The code assumes your list starts in column A, row 1, and has one alphanumeric character prefixing the numeric characters - please advise if this is not the case.
Are you familiar with inserting VBA code in your workbooks?
Right-click any sheet within the appropriate workbook, and select "View Code" from the pop-menu displayed.
When the "Microsoft Visual Basic" environment is displayed, look for a window named "Project - VBAProject"; if it is not shown, depress & hold down the [CTRL] key, press & release the [R] key, then release the [CTRL] key.
Locate the entry in the "Project - VBAProject" window for "VBAProject (<name of your .xls file>)", and right-click it.
Select "Insert->" then "Module" from the menu displayed, and then paste the following into the code window (most probably "Module1", but may be "Module" followed by a number) that is then shown:
' Start of Code...
Option Explicit
Public Sub Sort_Column_A()
' --------------------------
' Experts Exchange Question:
' Home \ All Topics \ Applications \ MS Office \ Excel
' https://www.experts-exchange.com/questions/21711174/Sorting-alpha-numeric-sequence.html
' Sorting alpha numeric sequence
'
' See also:
' https://www.experts-exchange.com/questions/21601371/sorting.html
' Sorting
'
' Copyright (c) 2005-2006 Clearlogic Concepts (UK) Limited
' N.Lee [ http://NigelLee.info ] - 20 October 2005 & 26 January 2006
' --------------------------
Dim objCell As Range
Dim strArray() As String
Dim vntSplit As Variant
On Error Resume Next
For Each objCell In Intersect([A1:A65536], ActiveSheet.UsedRange)
ReDim Preserve strArray(objCell.Row) As String
strArray(objCell.Row) = Left$(objCell, 1) & vbTab & Format$(Mid$(objCell, 2), "000000")
Next objCell
If (blnQuick_Sort_Strings(str
For Each objCell In Intersect([A1:A65536], ActiveSheet.UsedRange)
vntSplit = Split(strArray(objCell.Row
objCell = vntSplit(0) & Val(vntSplit(1))
Next objCell
End If
End Sub
Private Function blnQuick_Sort_Strings(ByRe
Optional ByRef lngLow_Value As Long = -1&, _
Optional ByRef lngHigh_Value As Long = -1&, _
Optional ByVal blnAlpha_Sort As Boolean = True) As Boolean
' --------------------------
' Experts Exchange Question:
' Home \ All Topics \ Applications \ MS Office \ Excel
' https://www.experts-exchange.com/questions/21601371/sorting.html
' Sorting
'
' Copyright (c) 2005 Clearlogic Concepts (UK) Limited
' N.Lee [ http://NigelLee.info ] - 20 October 2005
' --------------------------
Dim blnReturn As Boolean
Dim blnSwap As Boolean
Dim lngLow As Long
Dim lngHigh As Long
Dim lngPivot As Long
Dim lngPosLow As Long
Dim lngPosHigh As Long
Dim strPivot As Variant
On Error GoTo Err_blnQuick_Sort_Strings
blnReturn = False
lngLow = IIf(lngLow_Value > -1&, lngLow_Value, LBound(strArray))
lngHigh = IIf(lngHigh_Value > -1&, lngHigh_Value, UBound(strArray))
If lngLow >= lngHigh Then
blnQuick_Sort_Strings = True
Exit Function
End If
' If only 2 elements in this subdivision; swap them if out of order...
If (lngHigh - lngLow) = 1& Then
If (blnAlpha_Sort) Then
blnSwap = (strArray(lngLow) > strArray(lngHigh))
Else
blnSwap = (Val(strArray(lngLow)) > Val(strArray(lngHigh)))
End If
If (blnSwap) Then
Call strSwap(strArray(lngLow), strArray(lngHigh))
End If
blnQuick_Sort_Strings = True
Exit Function
End If
' Pick a pivot element at random & move it to the end...
lngPivot = CLng(Int(Rnd(1) * (lngHigh - lngLow) + 1&) + lngLow)
Call strSwap(strArray(lngHigh),
strPivot = UCase$(strArray(lngHigh))
Do
lngPosLow = lngLow
lngPosHigh = lngHigh
' Move in from both sides towards the pivot element...
If (blnAlpha_Sort) Then
Do While (lngPosLow < lngPosHigh) And (UCase$(strArray(lngPosLow
lngPosLow = lngPosLow + 1&
Loop
Do While (lngPosHigh > lngPosLow) And (UCase$(strArray(lngPosHig
lngPosHigh = lngPosHigh - 1&
Loop
Else
Do While (lngPosLow < lngPosHigh) And (Val(strArray(lngPosLow)) <= Val(strPivot))
lngPosLow = lngPosLow + 1&
Loop
Do While (lngPosHigh > lngPosLow) And (Val(strArray(lngPosHigh))
lngPosHigh = lngPosHigh - 1&
Loop
End If
' If we haven't reached the pivot element then two elements on either side are out of order & need swapping...
If lngPosLow < lngPosHigh Then
Call strSwap(strArray(lngPosLow
End If
Loop While (lngPosLow < lngPosHigh)
' Move the pivot element back to its proper place in the array...
Call strSwap(strArray(lngPosLow
' Recursively call the Sort procedure (pass the smaller subdivision first to use less stack space)...
blnReturn = True
If (lngPosLow - lngLow) < (lngHigh - lngPosLow) Then
blnReturn = blnQuick_Sort_Strings(strA
If (blnReturn) Then
blnReturn = blnQuick_Sort_Strings(strA
End If
Else
blnReturn = blnQuick_Sort_Strings(strA
If (blnReturn) Then
blnReturn = blnQuick_Sort_Strings(strA
End If
End If
Exit_blnQuick_Sort_Strings
On Error Resume Next
blnQuick_Sort_Strings = blnReturn
Exit Function
Err_blnQuick_Sort_Strings:
blnReturn = False
Resume Exit_blnQuick_Sort_Strings
End Function
Private Sub strSwap(ByRef strFirst As String, _
ByRef strSecond As String)
' --------------------------
' Experts Exchange Question:
' Home \ All Topics \ Applications \ MS Office \ Excel
' https://www.experts-exchange.com/questions/21601371/sorting.html
' Sorting
'
' Copyright (c) 2005 Clearlogic Concepts (UK) Limited
' N.Lee [ http://NigelLee.info ] - 20 October 2005
' --------------------------
Dim strTemp As String
On Error Resume Next
strTemp = strSecond
strSecond = strFirst
strFirst = strTemp
End Sub
' ...End of Code
Now depress & hold the [ALT] key, press & release [F11], then release the [ALT] key.
Save your workbook (to make sure the code is saved).
When you wish to run the code, hold the [ALT] key, press & release [F8], then release the [ALT] key.
Select "Sort_Column_A" from the list displayed in the "Macro" dialog window, and click [Run].
BFN,
fp.
(Graded already... oh well...)
Liam,
Thnx for the Points!
all the others: Thnx for interresting code!
Xanius
Thnx for the Points!
all the others: Thnx for interresting code!
Xanius
Matt:
That's awesome. Thanks for sharing.
Jaes
That's awesome. Thanks for sharing.
Jaes
Xanius is right, it needs to be split (unless you wanted to make a custom sort list, but I would only recommend that if you have a limited/short number of items to be sorted). The easiest way in my opinion is to highlight the entire column, then go to Data / Text to Columns, choose fixed width, and split it that way. If you could have C1 in the same column as ZZ3, and need them to be sorted by letter combination then number, you'd probably have to use a macro to split/sort it. If you'd like a macro to do the sorting for you, let us know and we can make one for you.
Matt