• 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

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

1.1 , 1.10 , 2 , 3.10 , 1.7 like this
 

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?
 
Back
Top