Trailing Zero

[LongToast]

Hi everyone.

I want to sort data in a range, the key is numbers, my data key look like this :

The problem is, in my real world, 1112 is actually less than 516, so to sort properly, i need to make 516 become 5160, 119 become 1190 and so on.

I use this code to sort :

With oWs.Sort
            .SortFields.Clear
            .SortFields.Add Key:=oWs.Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
            .SetRange oWs.Range("A1:K" & iLastRow)
            .Header = xlNo
            .Apply
        End With

Solution in my head is "format that range before sort action", so i add this code :

oWs.Range("A:A").NumberFormat = "#000"

oWs.Range("A:A").NumberFormat = "0000"

That codes doesn't work

I can't use looping because i have hundred thousand data in my range, looping on com object just make it slower, i can't use array because bubble sort on array is very slow, i can't use quick sort because it only support 1 key (i have to sort on multiple keys).


So, any suggestion?

 

[awoohaw]

I comparing two values against each other on array when sort process is done, my problem is in sorting part.

Then can I suggest, just as an exercise to see what is possible, to do it with formulas on worksheet and not with VBA?

Post #2 does this.

 

[LongToast]

Then can I suggest, just as an exercise to see what is possible, to do it with formulas on worksheet and not with VBA?

Post #2 does this.

This means, in this part :

TemporarySheet.Range("A1").Resize(UBound(arrData, 1), UBound(arrData, 2)).Value = arrData

I have to add formula to each cell, is that what you means?

 

[awoohaw]

This means, in this part :

TemporarySheet.Range("A1").Resize(UBound(arrData, 1), UBound(arrData, 2)).Value = arrData

I have to add formula to each cell, is that what you means?

Well, not for a permanent solution. But, to get an idea of what the actual comparison process is, as well as to convert your numbers into text that will complete the comparison.
i'm not a vba person.
but,
Convert the entire range on the "temporary sheet" to text, then sort.

Whatever the vba equivalent of this is:

1. rangeofcells =TEXT(rangeofcells,0)
2. SORT(rangeofcells,1,1)

 

[LongToast]

@awoohaw and @Joe4

 

[LongToast]

@awoohaw and @Joe4 Thanks for the idea, i guess i know what to do, you guys give me a thought, on original sheet i simply create another one column, i fill that new column with values come from the key column, but with extra 0 if it has 3 digit only, this new column i use as key for sorting on temporary sheet and key column i use for display.

 

[awoohaw]

I just did this in VBA, hope it gives you an idea. I do not use the same VBA for the arrays as you, however.
NOTE: I am not proficient at VBA, and I'm sure there are better versions of this, but it seems to work and give an idea of what needs to be done.

Sub SortAlpha()

Dim r As Range
Dim v As Variant
Dim c As Integer
c = 0
ActiveWorkbook.Sheets("Sheet1").Select
Set r = Range("A3:A7")
For Each cell In r
    v = cell.Value
    v = "'" & CStr(cell.Value)
    cell.Value = v

    Next cell
  

ActiveWorkbook.Sheets("Sheet1").Sort.SortFields.Clear

r.Sort _
    key1:=Range("A3"), _
    order1:=xlAscending, _
    DataOption1:=xlSortNormal, _
    Header:=xlNo
End Sub

It is this before it runs:

Book1
	A	B
1
2
3	119
4	1112
5	156
6	125
7	1
8
Sheet1

After it runs:
(the xl2bb copying changed the contents to values, but it does show as text in the workbook I use).
(Picture underneath).

Book1
	A	B
1
2
3	1
4	1112
5	119
6	125
7	156
8
Sheet1

 

[LongToast]

I just did this in VBA, hope it gives you an idea. I do not use the same VBA for the arrays as you, however.
NOTE: I am not proficient at VBA, and I'm sure there are better versions of this, but it seems to work and give an idea of what needs to be done.

Sub SortAlpha()

Dim r As Range
Dim v As Variant
Dim c As Integer
c = 0
ActiveWorkbook.Sheets("Sheet1").Select
Set r = Range("A3:A7")
For Each cell In r
    v = cell.Value
    v = "'" & CStr(cell.Value)
    cell.Value = v

    Next cell
 

ActiveWorkbook.Sheets("Sheet1").Sort.SortFields.Clear

r.Sort _
    key1:=Range("A3"), _
    order1:=xlAscending, _
    DataOption1:=xlSortNormal, _
    Header:=xlNo
End Sub

It is this before it runs:

Book1
	A	B
1
2
3	119
4	1112
5	156
6	125
7	1
8
Sheet1

After it runs:

Book1
	A	B
1
2
3	1
4	1112
5	119
6	125
7	156
8
Sheet1

Thank you sir, but the problem with this solution it's very very slow, you can test it with 2.000 data, it took 10 to 13 second to be done and i have hundred thousand data, because you looping on cell (com object), for me it's not an option, i will simply use Helper column to do this, i test it and only took 0,6 second for 2.000 row x 12 column data.

 

[awoohaw]

Did you turn off screen updating?
This at start of sub:

Application.ScreenUpdating = False

This at end of sub:

 Application.ScreenUpdating = True

It could be sped up with array processing probably, but that is beyond my expertise.
I'm pleased you found a solution. Please post a mini sheet (using xl2bb) or your formulas. As others searching for similar solutions may find it helpful.

 

[LongToast]

Did you turn off screen updating?
This at start of sub:

Application.ScreenUpdating = False

This at end of sub:

 Application.ScreenUpdating = True

It could be sped up with array processing probably, but that is beyond my expertise.
I'm pleased you found a solution. Please post a mini sheet (using xl2bb) or your formulas. As others searching for similar solutions may find it helpful.

Maybe i will do it tomorrow, it's 2 am in my place now and i type this message with half eye opened.

 

[awoohaw]

Okay, I just tried it with dummy data of 150000 cells, and it took about 8 seconds for me with screen updating off.
But, remember, the VBA I suggested is probably not the best. Your original array formula methodology could be faster, just update it with the Cstr function conversions and other necessary updates.

Best wishes.