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]

are you asking for alphabetical sorting? If so, just change your numeric values to text?

Book2
	A	B	C
1	Orig	4 digit	text
2	1112	1112	1112
3	1112	1112	1112
4	1112	1112	1112
5	1112	1112	1112
6	523	5230	523
7	523	5230	523
8	521	5210	521
9	516	5160	516
10	516	5160	516
11	516	5160	516
12	119	1190	119
13	115	1150	115
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=IF(LEN(A2)<4,A2*10,A2)
C2:C13	C2	=TEXT(A2,0)

 

[Joe4]

Number format just changes the display, not the underlying value. And all Excel functions and functionality (like sorting) run on the actual values, not their displays.

You can either do what awoohaw suggests, of for any numbers less than 1000, multiply them by 10 to make them for digit numbers (assuming that all numbers are at least 3 digits).

 

[LongToast]

are you asking for alphabetical sorting? If so, just change your numeric values to text?

Example :
119 > 1112

119 is bigger than 1112, as you can see, this is not common, but i need to make this happen, so what i thought is how to add 0 to 119 and become 1190 so :

1190 > 1112 ----> this is the goal so i can sort it properly.

 

[LongToast]

Number format just changes the display, not the underlying value. And all Excel functions and functionality (like sorting) run on the actual values, not their displays.

You can either do what awoohaw suggests, of for any numbers less than 1000, multiply them by 10 to make them for digit numbers (assuming that all numbers are at least 3 digits).

To do this i need loop right?

 

[Joe4]

To do this i need loop right?

That would be one way.
You could also use a helper column/formula like:

=IF(A2<1000,A2*10,A2)

then copy/paste special values over original values, then delete helper column. No loops involved that way.

 

[awoohaw]

To do this i need loop right?

what are you asking for loops for? Unless you're insisting on a VBA solution (which probably is not necessary):

Maybe you can explain the scenario you need this for?

Book2
	F	G
4	TRUE	="119">"1112"
5	TRUE	=119*10>1112
Sheet1

Cell Formulas
Range	Formula
F4	F4	="119">"1112"
G4:G5	G4	=FORMULATEXT(F4)
F5	F5	=119*10>1112

 

[LongToast]

what are you asking for loops for? Unless you're insisting on a VBA solution (which probably is not necessary):

Book2
	F	G
4	TRUE	="119">"1112"
5	TRUE	=119*10>1112
Sheet1

Cell Formulas
Range	Formula
F4	F4	="119">"1112"
G4:G5	G4	=FORMULATEXT(F4)
F5	F5	=119*10>1112

I sort it on temporary sheet, not on original sheet, the process is :

arrData = OriginalSheet.Range("A1:K" & LastRow).Value

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

'sort process on TemporarySheet here'
'after sorting i take that data back to array and use it to display in another sheet which has some format like color, space between data, etc'

 

[awoohaw]

I sort it on temporary sheet, not on original sheet, the process is :

arrData = OriginalSheet.Range("A1:K" & LastRow).Value

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

'sort process on TemporarySheet here'
'after sorting i take that data back to array and use it to display in another sheet which has some format like color, space between data, etc'

I'm confused. I don't see how that will do what you want to have done. Convert the values to text and then compare, is all I can say.

 

[LongToast]

I don't see how in that code how you are comparing two values against each other.

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