• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting Decimal Values

A

Abhijeet

Active Member
Hi

I have Number some values have decimal some values are only numbers so i want to sort these data smallest to largest. I am using =INT(A2) +(A2<0) and =IFERROR(RIGHT(A2,LEN(A2)-FIND(".",A2)),"") these 2 formulas if any one any other way to sort these data then please tell me
 

Attachments

  • Page number.xlsm
    20.6 KB · Views: 8
Test 'Filter' ( Tools -> Filter )
and
use always same decimal separator (I use ",").
 

Attachments

  • Page number.xlsm
    15.3 KB · Views: 6
Hi !

Abhijeet, following your private conversation,
I have not the willing to answer to your further threads
as long as you have not answered me in the former ...

Free advice : you will get help of anyone if you respect forum rules
with crystal clear initial post, attachment making sense, …
​
 
A Short Prief
1) Open my previous 'Uploaded File' 'Page number.xlsm'
2) Open 'Filter.png'
3) Click to open same kind of 'popup' like previous-photo from Excel-file.
'small box' right side of 'Page Number'-text
4) Test to click 'Ascending/Descending'-buttons effect.
5) Decimal separator; You wrote that You'll use values with or without decimals. So, use numbers! and use Your default decimal separator if need!
I use "," for it (like 1,4 or 1,03).
If You're using mixed decimal separator,
then sorting don't work normally as You want.
Okay?
 

Attachments

  • Filter.png
    Filter.png
    37.6 KB · Views: 8
Hi @vletm ,

I think there is a misunderstanding on what the OP requires.

Suppose the decimal numbers are 1.1 , 1.2 , 1.3 ,..., the 1.1 , 1.2 , 1.3 ,..., where there is only one digit after the decimal point , is not really 1.1 , 1.2 , 1.3 ,... They are to be treated as 1.01 , 1.02 , 1.03 ,..., at least for sorting purposes.

You will see that there are also numbers such as 1.10 , which is not the same as 1.1 ; in the sort order , 1.10 should appear after 1.9 , which means there should be two sort levels , one for the integer part and the other for the decimal part , where the decimal part is to be sorted so that .1 appears before .10 , even though mathematically both may be identical.

Narayan
 
Code: 
Sub testdd()
    Dim array1(), array2()

    array1 = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value2
    lf = UBound(array1, 1)
    ReDim array2(1 To lf, 1 To 2)
     
   
    For l = 1 To lf
        aa = Split(array1(l, 1), ".")
        array2(l, 1) = Val(aa(0))
        If UBound(aa) > 0 Then array2(l, 2) = Val(aa(1))
    Next
    Range("b2:c" & lf).Value2 = array2
End Sub

I have classifier array, but is to sort by columns
then I make the adaptation

but I did not understand the fact that 1.7 be after 3.10

Abhijeet said:
1.1 , 1.10 , 2 , 3.10 , 1.7 like this
Click to expand...
 

Just needs a sort with a criteria per column B & C …

No other way 'cause of this mathematical aberration !
Such difficult to enter 1.01 instead of 1.1 ?‼‼

So Abhijeet, please never pilot a boat neither a plane !
​
 
Code: 
Sub testdd()
    Dim array1(), array2(), va As String

    array1 = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value2
    lf = UBound(array1, 1)
    ReDim array2(1 To lf, 1 To 2)

    For L = 1 To lf
        Aa = Split(array1(L, 1), ".")
        If UBound(Aa) > 0 Then
            If Len(Aa(1)) = 1 Then
                array2(L, 1) = Val(Aa(0) & 0 & Aa(1))
            Else
                array2(L, 1) = Val(Aa(0) & Aa(1))
            End If
        Else
            array2(L, 1) = Val(Aa(0) & "00")
        End If
    Next



    ci1 = 1
    inC = ci1:
    i = inC + 1
    Do
        v1 = array2(inC, 1)
        v2 = array2(inC + 1, 1)
        va1 = array1(inC, 1)
        va2 = array1(inC + 1, 1)
        A = v1
        b = v2
        Aa = va1
        ba = va2
        If A > b Then

            array2(inC, 1) = b: c = A
            array2(inC + 1, 1) = c
            array1(inC, 1) = ba: ca = Aa
            array1(inC + 1, 1) = ca
            If inC > ci1 Then inC = inC - 1
        Else
            inC = i: i = i + 1
        End If
    Loop Until inC = lf
    '    For L = 1 To lf
    '        aa = Split(array1(L, 1), ".")
    '        array2(L, 1) = Val(aa(0))
    '        If UBound(aa) > 0 Then array2(L, 2) = Val(aa(1))
    '    Next

    Range("a2:a" & lf + 1).Value2 = array1
    Range("b2:c" & lf + 1).Value2 = array2
End Sub
 
Hi @NARAYANK991 No misunderstanding!
If just use value 1.1, Excel can use it like date ( 1st of January ), especially then cell's Category is 'General'. Difference of 1.1 and 1,01 is clear for me as well as how to sort with like 'text'. I was also worry, if Abhijeet suddenly want to use values like '1.2.3' and so. That kind of values would be more interesting. Okay?
 
You must log in or register to reply here.
Back
Top