Sorting in excel using sub, sorting not proper

Question

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

On the worksheet you would use a helper column. In VBA, you will have to put it into an array, split each element of the array into the largest number of pieces that any element has and then sort the arrays of arrays and put it back into the worksheet. — user4039065
– user4039065, Commented Aug 26, 2015 at 9:42
btw, there is a Header:=xlYes or a Header:=xlNo option. It's probably not a good idea to let Excel guess since you should know if your data has a header or not. — user4039065
– user4039065, Commented Aug 26, 2015 at 9:51
Is 1 and 1.1 part of the complete series of the mutli-level list? What is the maximum number of 'pieces'? — user4039065
– user4039065, Commented Aug 26, 2015 at 9:56
now my ids will be like 1.1.1 , 1.1.12 no levels, format of my test case ids is such, i tried helper column it still sorts the same way. — Sumayya Khalid
– Sumayya Khalid, Commented Aug 26, 2015 at 10:01

user4039065 · Accepted Answer · 2015-08-26 10:15:40Z

1

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

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.

answered Aug 26, 2015 at 10:15

user4039065

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting in excel using sub, sorting not proper

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related