How to sort outline numbers in "numerical" order?

Question

For instance, I would like these 6 numbers.

Currently when I use the sort method it puts 6.6.1.1.13 first and 6.6.1.1.2 later.

Before Sort

  6.6.1.1
  6.6.1.1.1
  6.6.1.1.13
  6.6.11.14
► 6.6.1.1.2

What I Want It To Look Like After Sort

  6.6.1.1
  6.6.1.1.1
► 6.6.1.1.2
  6.6.1.1.13
  6.6.11.14

Answer 1

Unfortunately the only fully general way to sort outline numbers (or more formally, "path-indexes") is with a custom comparison function. Unfortunately, the Excel sorting operations and functions do not support such a feature (not even from VBA). The Excel preferred way is with custom lists, but these are not workable for path-indexes.

This leaves two choices:

1. Do your sorting entirely in VBA: This works (I've done it) but is pretty involved and messy. Or,

2. Use a Helper Column with a VBA function: This works but is not fully general, because you have to know ahead of time what the maximum index values will be.

of these, #2 above is by far the simpler option, but it does have limitations (explained below).

Basically what we want is a VBA function that can take a string like "6.6.11.14" and make it always sortable in path index order. The problem with this string is that in text order two digit indexes like ".11" and ".14" come before ".2" rather than after it.

The obvious way to fix this is to fix this is to convert all indexes into 2-digit numbers with leading zeroes. So, 6.6.11.14 would become 06.06.11.14 and crucially 6.6.2.1 would become 06.06.02.01. Now these two path-index values will sort correctly use text sorting.

The catch, however, is that this is only true if each individual index number is never greater than two digits (99). Thus, 06.07.99 sorts correctly, but 06.07.110 does not under this scheme. This is easily fixable by simply raising it from two digits to three digits, but again, the catch is that you have to know this ahead of time.

So assuming that we do know ahead of time what the maximum size/(number of digits) will be for any single index number, we can use the following VBA function to reformat your outline numbers for a helper column:

Public Function OutlineSortingFormat(OutlineNumber As String, Digits As Integer) As String
    Dim PathIndexes() As String
    Dim Zeroes As String
    Dim i As Integer
    
    Zeroes = "0000000000"
    
    PathIndexes = Split(OutlineNumber, ".")
    
    For i = 0 To UBound(PathIndexes)
        PathIndexes(i) = Right(Zeroes & PathIndexes(i), Digits)
    Next i
    
    OutlineSortingFormat = Join(PathIndexes, ".")
End Function

This just splits the outline number into individual numeric strings, prefixes the correct amount of zeroes and then concatenates them back into a sortable outline number.

You then apply this by making a helper column and then using the function like so:

=OutlineSortingFormat(M3,2)

Where M is the column that has your unformatted outline indexes and the second parameter (, 2)) indicates that your want all index numbers filled (and truncated) to 2 digits. Then instead of sorting on your original outline numbers, your sort on the "helper column" containing the reformatted values.

Answer 2

Manual method

Use the Text to Column function and separate out your headers using "." as a delimiter.

When you are done select all the data as follows:

Perform a sort on the selected data.

Note: My data has headers has been selected and column 6 and 7 come up a A to Z as they are currently empty and it defaults to alphabetical sort as a result. The alphabetical sort can be added by adding a dummy row of data at the start or end of your data to be sorted. This is done by either adding all 0's or a number larger than any number in your list to all columns.

After selecting ok your "Combined" data will be sorted numerically based on the outline numbers to the right.

Answer 3

Here is some code for multiple purposes.

The first function is a UDF which if wanted could be called from the worksheet to be used as a helper function. Therefore it's easy enough to alter the pad length if required for sorting.

The second code is a little more involved but inserts a column next to the table, adds the helper function, sorts then deletes the helper column to leave the sheet structure as before.

SortColumn should be defined as the column index where the indices are. i.e. if in the first column of the designated table then it would be set to '1'

Public Function PadIndices(Cell As Range, PadLength As Long, Optional Delimiter As String) As String
    If Cell.Count > 1 Then Exit Function
    If Delimiter = "" Then Delimiter = "."
    
    Dim Arr As Variant: Arr = Split(Cell.Value, Delimiter)
    Dim i As Long: For i = LBound(Arr) To UBound(Arr)
        If Len(Arr(i)) < PadLength Then Arr(i) = WorksheetFunction.Rept("0", PadLength - Len(Arr(i))) & Arr(i)
    Next i
    PadIndices = Join(Arr, Delimiter)
End Function

Sub SortByIndices()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim Table As Range: Set Table = ws.Range("H7:I11")
    Dim PadLength As Long: PadLength = 2
    Dim SortColumn As Long: SortColumn = 1
    
    Table.Columns(1).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Dim SortRange As Range: Set SortRange = Table.Columns(1).Offset(0, -1)
    SortRange.Formula2R1C1 = "=PadIndices(RC[" & SortColumn & "], " & PadLength & ")"
    
    With ws.Sort.SortFields
        .Clear
        .Add2 Key:=SortRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    
    With ws.Sort
        .SetRange Application.Union(Table, SortRange)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    SortRange.Delete Shift:=xlToLeft
    
End Sub

Answer 4

You could make a helper column in which you remove the points and sort that helper column.

=NUMBERVALUE(SUBSTITUTE(E4;".";))

Answer 5

A) User defined function without the need of a help column

In order to be able to sort outline numbers, you have to bring the individual numerical values to a well defined uniform number format (like e.g. "00" if numbers don't exceed 99 as assumed default; note the flexible String() function in section b)).

This dynamic array approach allows argument inputs of any range definitions (plus optional digit maxima) like

• =Outline(A5:A10) to sort one column (with a 2-digits default maximum) or even
• =Outline(A2:E4, 3) over a multicolumn range (with an explicit 3-digits maximum)

Note: tested with the newer dynamic features of Office 2019+/MS365; for backward compatibility you would have to change the TextJoin() function and possibly enter =Outline(...) as array formula using CSE (Ctrl+Shift+Enter).

Function Outline(rng As Range, Optional ByVal digits As Long = 2)
'Date: 2022-01-09
'Auth: https://stackoverflow.com/users/6460297/t-m
'a) create unordered 1-dim array from any contiguous range
    Dim myFormula As String
    myFormula = "TextJoin("","",True," & rng.Address(False, False) & ")"
    Dim codes
    codes = Split(rng.Parent.Evaluate(myFormula), ",")
'b) add leading zeros via number format
    Dim i As Long
    For i = LBound(codes) To UBound(codes)
        Dim tmp: tmp = Split(codes(i), ".")
        Dim ii As Long
        For ii = LBound(tmp) To UBound(tmp)
            tmp(ii) = Format(CInt(tmp(ii)), String(digits, "0"))
        Next ii
        codes(i) = Join(tmp, ".")   ' join to entire string element
        Debug.Print i, codes(i)
    Next i
'c) sort
    BubbleSort codes                ' << help proc BubbleSort
'd) remove leading zeros again
    For i = LBound(codes) To UBound(codes)
        For ii = 1 To digits - 1    ' repeat (digits - 1) times
            codes(i) = Replace(codes(i), ".0", ".")
            If Left(codes(i), 1) = "0" Then codes(i) = Mid(codes(i), 2)
        Next
    Next
'e) return function result
    Outline = Application.Transpose(codes)
End Function

Help procedure BubbleSort

Sub BubbleSort(arr)
'Date: 2022-01-09
'Auth: https://stackoverflow.com/users/6460297/t-m
    Dim cnt As Long, nxt As Long, temp
    For cnt = LBound(arr) To UBound(arr) - 1
        For nxt = cnt + 1 To UBound(arr)
            If arr(cnt) > arr(nxt) Then
                temp = arr(cnt)
                arr(cnt) = arr(nxt)
                arr(nxt) = temp
            End If
        Next nxt
    Next cnt
End Sub

---

B) Just for fun: alternative single-formula approach (with restricted number range)

Instead of extending the digit formats, I played with the idea to restrict the numeric display by executing a temporary hexadecimal replacement.

Note that this approach based on a single-formula evaluation allows outline sub-numbers only within a numeric range from 1 to 15 (as numbers 10 to 15 get replaced by characters A to F), but might be sufficient for low hierarchy depths! Furthermore it includes a tabular Sort() function available only in Excel version MS365!

Function Outline(rng As Range)
'Site: https://stackoverflow.com/questions/70565436/how-to-sort-outline-numbers-in-numerical-order
'Date: 2022-01-09
'Auth: https://stackoverflow.com/users/6460297/t-m
'Meth: hex replacements + sort; assuming chapters from (0)1 to 15 (10=A,11=B..15=F)
'Note: allows outline sub-numbers only up to 15! Needs Excel version MS365.
    Dim pattern
    pattern = String(6, "X") & "Sort(" & String(6, "X") & "$,15,""F""),14,""E""),13,""D""),12,""C""),11,""B""),10,""A"")),""A"",10),""B"",11),""C"",12),""D"",13),""E"",14),""F"",15)"
    pattern = Replace(Replace(pattern, "$", rng.Address(False, False)), "X", "Substitute(")
    Outline = rng.Parent.Evaluate(pattern)
End Function