CODEWITHSUNDEEP
excelsortingvba
Sumayya Khalid
Sumayya Khalid

Reputation: 31

Sorting in excel using sub, sorting not proper

I want to sort a particular column in my sheet which has my test case IDs, My current sort sub (given below) is sorting it like 

 1.1.1
 1.1.12
 1.1.15
 1.1.2
 1.1.22
 1.1.3

is it because it is taking it as alphanumeric data?

how to get it to sort properly like?

1.1.1
1.1.2
1.1.3
1.1.12
1.1.15
1.1.22

My sub function:

    Function sortSheet(ByVal sheet As Excel.Worksheet)
    Dim oneRange As Range
    Dim aCell As Range
    Dim lastRow As Long, lastCol As Integer

   'Calculating the last row and column
   lastRow = sheet.Cells(Rows.Count, 1).End(xlUp).row
   lastCol = sheet.Cells(1, Columns.Count).End(xlToLeft).Column

   'Setting the range in which sorting is to be done
    Set oneRange = sheet.Range(sheet.Cells(2, 1), sheet.Cells(lastRow, lastCol))

   'Setting the range according to which it will be sorted
    Set aCell = sheet.Range("A2")
   'Sorting
    oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlGuess

    End Function

Upvotes: 3

Views: 310

Answers (1)

user4039065
user4039065

Reputation:

The right formula in a helper column will make quick work of this.

            Sorting with a Helper Column

The formula in B2 is,

=TEXT(LEFT(A2, FIND(".", A2)-1), "000")&TEXT(MID(A2, FIND(".", A2)+1, 9), "000")&TEXT(MID(A2, FIND(".", A2, FIND(".", A2)+1)+1, 9), "000")

Fill down as necessary. This works because there is a static number of 'pieces' to the multi-level ordinals. The formula would be more complicated if it had to accommodate 1, 2 or 3 sections.

Upvotes: 1

Related Questions