The principle of the following solution is to insert a new column in which the cells have a formula which calculates a "sortable code" of each cell of the column that you want to sort.
If you sort this new column, the rows will be sorted in the ASCII order (0-9, A-Z, _).
It should be able to handle any number of rows. On my laptop, the calculation of cells takes 1 minute for 130.000 rows. There are two VBA functions, one for ASCII and one for EBCDIC. It's very easy to define other character sets.
Steps:
- Create a module in your Excel workbook and place the code below.
- Close the VB editor otherwise it will run slowly.
- In the worksheet that you want to sort, insert one column for each column you want to sort, for instance let's say the sort is to be done for column A, create a new column B, in the cell
B1 insert the formula =SortableCodeASCII(A1) and do the same for all the cells of column B (up to the last row of column A).
- Make sure that the calculation of formulas is over (it takes 1 minute for 130.000 rows on my laptop), otherwise if you sort, the order will be incorrect because formulas are not yet calculated. You see the progress indicator (percentage) on the status bar at the bottom of the Excel window. If you don't see it, press Ctrl+Alt+F9.
- Sort on column B. The values in column A should be sorted according to the ASCII order (
0-9, A-Z, _)
Good luck!
Option Compare Text 'to make true "a" = "A", "_" < "0", etc.
Option Base 0 'to start arrays at index 0 (LBound(array) = 0)
Dim SortableCharactersASCII() As String
Dim SortableCharactersEBCDIC() As String
Dim SortableCharactersTEST() As String
Sub ResetSortableCode()
'Run this subroutine if you change anything in the code of this module
'to regenerate the arrays SortableCharacters*
Erase SortableCharactersASCII
Erase SortableCharactersEBCDIC
Erase SortableCharactersTEST
Call SortableCodeASCII("")
Call SortableCodeEBCDIC("")
Call SortableCodeTEST("")
End Sub
Function SortableCodeASCII(text As String)
If (Not Not SortableCharactersASCII) = 0 Then
SortableCharactersASCII = getSortableCharacters( _
orderedCharacters:=" !""#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}" & ChrW(126) & ChrW(127))
End If
SortableCodeASCII = getSortableCode(text, SortableCharactersASCII)
End Function
Function SortableCodeEBCDIC(text As String)
If (Not Not SortableCharactersEBCDIC) = 0 Then
SortableCharactersEBCDIC = getSortableCharacters( _
orderedCharacters:=" ¢.<(+|&!$*);-/¦,%_>?`:#@'=""abcdefghi±jklmnopqr~stuvwxyz^[]{ABCDEFGHI}JKLMNOPQR\STUVWXYZ0123456789")
End If
SortableCodeEBCDIC = getSortableCode(text, SortableCharactersEBCDIC)
End Function
Function SortableCodeTEST(text As String)
If (Not Not SortableCharactersTEST) = 0 Then
SortableCharactersTEST = getSortableCharacters( _
orderedCharacters:="ABCDEF 0123456789_")
End If
SortableCodeTEST = getSortableCode(text, SortableCharactersTEST)
End Function
Function getSortableCharacters(orderedCharacters As String) As String()
'Each character X is assigned another character Y so that sort by character Y will
'sort character X in the desired order.
maxAscW = 0
For i = 1 To Len(orderedCharacters)
If AscW(Mid(orderedCharacters, i, 1)) > maxAscW Then
maxAscW = AscW(Mid(orderedCharacters, i, 1))
End If
Next
Dim aTemp() As String
ReDim aTemp(maxAscW)
j = 0
For i = 1 To Len(orderedCharacters)
'Was a character with same "sort weight" previously processed ("a" = "A")
For i2 = 1 To i - 1
If AscW(Mid(orderedCharacters, i, 1)) <> AscW(Mid(orderedCharacters, i2, 1)) _
And Mid(orderedCharacters, i, 1) = Mid(orderedCharacters, i2, 1) Then
'If two distinct characters are equal when case is ignored (e.g. "a" and "A")
'(this is possible only because directive "Option Compare Text" is defined at top of module)
'then only one should be used (either "a" or "A" but not both), so that the Excel sorting
'does not vary depending on sorting option "Ignore case".
Exit For
End If
Next
If i2 = i Then
'NO
aTemp(AscW(Mid(orderedCharacters, i, 1))) = Format(j, "000")
j = j + 1
Else
'YES "a" has same weight as "A"
aTemp(AscW(Mid(orderedCharacters, i, 1))) = aTemp(AscW(Mid(orderedCharacters, i2, 1)))
End If
Next
'Last character is for any character of input text which is not in orderedCharacters
aTemp(maxAscW) = Format(j, "000")
getSortableCharacters = aTemp
End Function
Function getOrderedCharactersCurrentLocale(numOfChars As Integer) As String
'Build a string of characters, ordered according to the LOCALE order.
' (NB: to order by LOCALE, the directive "Option Compare Text" must be at the beginning of the module)
'Before sorting, the placed characters are: ChrW(0), ChrW(1), ..., ChrW(numOfChars-1), ChrW(numOfChars).
'Note that some characters are not used: for those characters which have the same sort weight
' like "a" and "A", only the first one is kept.
'For debug, you may define constdebug=48 so that to use "printable" characters in sOrder:
' ChrW(48) ("0"), ChrW(49) ("1"), ..., ChrW(numOfChars+47), ChrW(numOfChars+48).
sOrder = ""
constdebug = 0 'Use 48 to help debugging (ChrW(48) = "0")
i = 34
Do Until Len(sOrder) = numOfChars
Select Case constdebug + i
Case 0, 7, 14, 15: i = i + 1
End Select
sCharacter = ChrW(constdebug + i)
'Search order of character in current locale
iOrder = 0
For j = 1 To Len(sOrder)
If AscW(sCharacter) <> AscW(Mid(sOrder, j, 1)) And sCharacter = Mid(sOrder, j, 1) Then
'If two distinct characters are equal when case is ignored (e.g. "a" and "A")
'("a" = "A" can be true only because directive "Option Compare Text" is defined at top of module)
'then only one should be used (either "a" or "A" but not both), so that the Excel sorting
'does not vary depending on sorting option "Ignore case".
iOrder = -1
Exit For
ElseIf Mid(sOrder, j, 1) <= sCharacter Then
'Compare characters based on the LOCALE order, that's possible because
'the directive "Option Compare Text" has been defined.
iOrder = j
End If
Next
If iOrder = 0 Then
sOrder = ChrW(constdebug + i) & sOrder
ElseIf iOrder = Len(sOrder) Then
sOrder = sOrder & ChrW(constdebug + i)
ElseIf iOrder >= 1 Then
sOrder = Left(sOrder, iOrder) & ChrW(constdebug + i) & Mid(sOrder, iOrder + 1)
End If
i = i + 1
Loop
'Last character is for any character of input text which is not in orderedCharacters
sOrder = sOrder & ChrW(constdebug + numOfChars)
getOrderedCharactersCurrentLocale = sOrder
End Function
Function getSortableCode(text As String, SortableCharacters() As String) As String
'Used to calculate a sortable text such a way it fits a given order of characters.
'Example: instead of order _, 0-9, Aa-Zz you may want 0-9, Aa-Zz, _
'Will work only if Option Compare Text is defined at the beginning of the module.
getSortableCode = ""
For i = 1 To Len(text)
If AscW(Mid(text, i, 1)) < UBound(SortableCharacters) Then
If SortableCharacters(AscW(Mid(text, i, 1))) <> "" Then
getSortableCode = getSortableCode & SortableCharacters(AscW(Mid(text, i, 1)))
Else
'Character has not an order sequence defined -> last in order
getSortableCode = getSortableCode & SortableCharacters(UBound(SortableCharacters))
End If
Else
'Character has not an order sequence defined -> last in order
getSortableCode = getSortableCode & SortableCharacters(UBound(SortableCharacters))
End If
Next
'For two texts "a1" and "A1" having the same sortable code, appending the original text allows using the sort option "Ignore Case"/"Respecter la casse"
getSortableCode = getSortableCode & " " & text
End Function
0-9, A-Z, _(the order may also depend on the locale but it's rare). I guess a VBA subroutine could be created to do the same with the functionasc()(asc("0") < asc("A") < asc("_")); it could read all cell values, select distinct values, sort them internally, create a "custom order" and do the sorting based on this custom order. Performance is low: with a column of 4000 cells, each having a unique value, the sorting takes 8 seconds. – SynchrocyclotronaandAhave the same weight in French (order in English:_ < 0 < 9 < a91 < A92 < a93). This can be done identically in SAP, if you use ABAPSORT table AS TEXT(order in English:_ < 0 < 9 < a91 < A92 < a93). I guess you can do the same with SQL. Conclusion: it's more simple to change the order of table rows in ABAP or SQL than in Excel. – Synchrocyclotron