Number Sorting Problem on Excel

ceoeric9 · Jul 11, 2011

Hi,

I'm currently working on a spreadsheet project with a list of over 6,000 numbers and words (ie. APP-BB.2.3, etc...)

I'm trying to get this is in order because excel seems to sort the numbers in a weird way where it would start off with 1 but skip to 10 and then 4.1... would show up.

here is an example...
1.1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
1.2<o

></o

>
1.3.1<o

></o

>
1.3.2.a<o

></o

>
1.3.2.b<o

></o

>
1.3.2.c<o

></o

>
1.3.2.d<o

></o

>
1.3.2.e<o

></o

>
1.3.2.f<o

></o

>
1.3.2.g<o

></o

>
1.3.2.h<o

></o

>
1.3.2.i<o

></o

>
1.3.2.j<o

></o

>
1.3.2.k<o

></o

>
1.3.2.l<o

></o

>
1.3.2.m<o

></o

>
10.1.1<o

></o

>
10.1.1.a<o

></o

>
10.1.1b<o

></o

>
10.1.2<o

></o

>
10.1.2<o

></o

>
10.1.3.1<o

></o

>
10.1.3.2<o

></o

>
10.1.3.2<o

></o

>
10.1.3.2<o

></o

>
10.1.3.2<o

></o

>
10.1.3.2<o

></o

>
10.1.3.3<o

></o

>
10.1.3.3<o

></o

>
10.1.3.3<o

></o

>
10.1.4<o

></o

>
10.1.5<o

></o

>
10.1.5<o

></o

>
10.1.5<o

></o

>
10.1.5<o

></o

>
10.1.6<o

></o

>
10.2<o

></o

>
10.2.1.1<o

></o

>
10.2.1.2<o

></o

>
10.2.1.2<o

></o

>
10.2.1.3<o

></o

>
10.2.1.3.a<o

></o

>
10.2.1.3.b<o

></o

>
10.2.1.3.c<o

></o

>
10.2.10.1<o

></o

>
10.2.10.2<o

></o

>
10.2.10.3<o

></o

>
10.2.10.3<o

></o

>
10.2.10.4<o

></o

>
10.2.11<o

></o

>
10.2.12<o

></o

>
10.2.13.1<o

></o

>
10.2.13.2<o

></o

>
10.2.13.2<o

></o

>
10.2.14.1<o

></o

>
10.2.14.2<o

></o

>
10.3.1<o

></o

>
10.2.2<o

></o

>
10.2.2<o

></o

>
10.2.2<o

></o

>
10.2.2<o

></o

>
10.2.2<o

></o

>
10.2.2<o

></o

>
10.2.3<o

></o

>
10.2.4<o

></o

>
10.2.4<o

></o

>
10.2.4<o

></o

>
10.2.4.1<o

></o

>
10.2.4.1<o

></o

>
10.2.4.1<o

></o

>
10.2.4.2<o

></o

>
10.2.4.2<o

></o

>
10.2.4.3<o

></o

>
10.2.4.4<o

></o

>
10.2.4.5<o

></o

>
10.2.4.6<o

></o

>
10.2.4.6<o

></o

>
10.2.4.6<o

></o

>
10.2.4.6<o

></o

>
10.2.4.7<o

></o

>
10.2.5.1<o

></o

>
10.2.5.2<o

></o

>
10.2.6<o

></o

>
10.2.6<o

></o

>
10.2.6<o

></o

>
10.2.6<o

></o

>
10.2.6<o

></o

>
10.2.6<o

></o

>
10.2.6<o

></o

>
10.2.7<o

></o

>
10.2.7<o

></o

>
10.2.8<o

></o

>
10.2.8<o

></o

>
10.2.8<o

></o

>
10.2.8<o

></o

>
10.2.8<o

></o

>
10.2.8.1<o

></o

>
10.2.8.2.1<o

></o

>
10.2.8.2.1<o

></o

>
10.2.8.2.1<o

></o

>
10.2.8.2.1<o

></o

>
10.2.8.2.1<o

></o

>
10.2.8.2.1<o

></o

>
10.2.8.2.2<o

></o

>
10.2.8.2.2<o

></o

>
10.2.8.2.2<o

></o

>
10.2.8.2.2<o

></o

>
10.2.8.2.2<o

></o

>
10.2.8.3<o

></o

>
10.2.8.4<o

></o

>
10.2.8.4<o

></o

>
10.2.9<o

></o

>
10.2.9.1<o

></o

>
10.2.9.2<o

></o

>
10.2.9.2<o

></o

>
10.2.9.4<o

></o

>
10.3.10.1<o

></o

>
10.3.10.1<o

></o

>
10.3.10.2<o

></o

>
10.3.10.2<o

></o

>
10.3.11<o

></o

>
10.3.11.1<o

></o

>
10.3.11.2<o

></o

>
10.3.11.2<o

></o

>
10.3.14<o

></o

>
10.3.14<o

></o

>
10.3.14.1<o

></o

>
10.3.14.1<o

></o

>
10.3.17<o

></o

>
10.3.17<o

></o

>
10.3.17<o

></o

>
10.4.1<o

></o

>
10.3.2.1<o

></o

>
10.3.2.1<o

></o

>
10.3.2.1<o

></o

>
10.3.2.1<o

></o

>
10.3.2.1<o

></o

>
10.3.2.2<o

></o

>
10.3.2.2<o

></o

>
10.3.2.2<o

></o

>
10.3.2.3<o

></o

>
10.3.2.3<o

></o

>
10.3.3<o

></o

>
10.3.4<o

></o

>
10.3.5<o

></o

>
10.3.6<o

></o

>
10.3.6<o

></o

>
10.3.6.1<o

></o

>
10.3.6.1<o

></o

>
10.3.6.1<o

></o

>
10.3.6.1<o

></o

>
10.3.6.2<o

></o

>
10.3.6.2<o

></o

>
10.3.6.2<o

></o

>
10.3.6.2<o

></o

>
10.3.6.2<o

></o

>
10.3.6.3<o

></o

>
10.3.7<o

></o

>
10.3.8<o

></o

>
10.3.8<o

></o

>
10.3.8<o

></o

>
10.3.9<o

></o

>
10.3.9<o

></o

>
10.3.9<o

></o

>
10.3.9<o

></o

>
10.3.9<o

></o

>
10.4.2<o

></o

>
10.4.2<o

></o

>
10.4.2<o

></o

>
10.4.2<o

></o

>
10.4.2<o

></o

>
10.4.3<o

></o

>
10.4.4.1<o

></o

>
10.4.4.1<o

></o

>
10.4.4.2<o

></o

>
4.1<o

></o

>
4.1<o

></o

>
4.2.1<o

></o

>
4.2.2<o

></o

>
4.3.1<o

></o

>
4.3.1.1<o

></o

>
4.3.1.2<o

></o

>

Please help! =)

mirabeau · Jul 11, 2011

Would you be able to present a shorter example showing:

1. your original set of numbers

2. after Excel has sorted them

3. how you would like them sorted

ceoeric9 · Jul 11, 2011

So here is one specific example.
After sorting the column A, the list seems to go in order from 1-44 but immediately under 44.3(suppose to be the last number) 10.1.1 shows up and the numbers start listing from 10.1.1, 10.1.1.a, 10.1.1.b

43.9<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
44.1<o

></o

>
44.2<o

></o

>
44.3<o

></o

>
10.1.1<o

></o

>
10.1.1.a<o

></o

>

I'd really like to find out a way to put 10.1.1 and 10.1.1.a before 43.9 with the rest of the 10.1s

mikerickson · Jul 11, 2011

Is the text aligned left or right or is the alignment unspecified?

It may be the case that Excel is reading 44.3 as a number and 10.1.1 as text and alphabetizing accordingly.

ceoeric9 · Jul 11, 2011

mikerickson said:
Is the text aligned left or right or is the alignment unspecified?

It may be the case that Excel is reading 44.3 as a number and 10.1.1 as text and alphabetizing accordingly.

The numbers are aligned to the left.
Actually I forgot to also add that before 10.1.1, 1.3.1 shows up. So...

43.9
44.1
44.2
44.3
1.3.1
1.3.1.a
1.3.1.b
10.1.1
...

mikerickson · Jul 12, 2011

The only way that I can see is to convert those values into a standardized format
e.g. 1.2.13.a > 001.002.013.00a
then sort and then convert back.

Code:

Sub test()
    Dim keyColumn As Range, keyArray As Variant
    Dim splitCount As Long, maxLen As Long
    Dim appendStr As String, formatStr As String
    Dim workStr As String, subString As String
    Dim Size As Long
    Dim i As Long, j As Long
    Dim outArray As Variant
    With Sheet1.Range("A:A")
        Set keyColumn = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    Size = keyColumn.Rows.Count
    keyArray = Application.Transpose(keyColumn.Value)
    
    With keyColumn
        maxLen = Evaluate("max(LEN(" & .Address(, , , True) & "))")
        splitCount = Evaluate("max(LEN(" & .Address(, , , True) & ")-len(SUBSTITUTE(" & .Address(, , , True) & ", ""."", """")))")
    End With
    
    outArray = keyArray
    appendStr = String(splitCount, ".")
    formatStr = String(maxLen, "0")
    
    For i = 1 To Size
        outArray(i) = keyArray(i) & appendStr
        workStr = vbNullString
        For j = 0 To splitCount
            subString = Split(outArray(i), ".")(j)
            subString = Right(formatStr & subString, maxLen)
            workStr = workStr & "." & subString
        Next j
        outArray(i) = Mid(workStr, 2)
    Next i
    
    Range("C1").Resize(UBound(outArray)).Value = Application.Transpose(outArray)
    
    Rem SORT HERE
    
    For i = 1 To Size
        workStr = vbNullString
        For j = 0 To splitCount
            subString = Split(outArray(i), ".")(j)
            If IsNumeric(subString) Then
                If subString = formatStr Then
                    subString = vbNullString
                Else
                    subString = Format(Val(subString), "@")
                End If
            Else
                subString = Replace(subString, "0", vbNullString)
            End If
            workStr = workStr & "." & subString
        Next j
        For j = 1 To splitCount
            workStr = Replace(workStr, "..", ".")
        Next j
        If workStr Like "*." Then
            workStr = Left(workStr, Len(workStr) - 1)
        End If
        outArray(i) = Mid(workStr, 2)
    Next i
    
    Range("E1").Resize(UBound(outArray)).Value = Application.Transpose(outArray)
    
End Sub

Number Sorting Problem on Excel

ceoeric9

New Member

Excel Facts

mirabeau

Banned user

ceoeric9

New Member

mikerickson

MrExcel MVP

ceoeric9

New Member

mikerickson

MrExcel MVP

Forum statistics

Share this page

Number Sorting Problem on Excel

ceoeric9

New Member

Excel Facts

mirabeau

Banned user

ceoeric9

New Member

mikerickson

MrExcel MVP

ceoeric9

New Member

mikerickson

MrExcel MVP

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