How to sort a range in excel in a column?

user_14283168 0
Viewed 39

I want to sort a column from smallest to largest. The column is like:

Day_Cluster
(0,5]
(10,15]
(5,10]
(15,20]

I want to sort from smallest to largest but it is not happening in MS Excel. How to achieve this?

Expected output:
(0,5]
(5,10]
(10,15]
(15,20]
excel-formula excel
1 Answers

A Special Sort

Manual

  • If your data is in column A, and you have data in column B then right-click in the column header of column B (selecting the whole column) and select Insert.

  • If your decimal separator is a period (dot) then use this formula in cell B1:

    =VALUE(SUBSTITUTE(MID(A1,2,LEN(A1)-2),",","."))

    and copy down as needed.

  • If your decimal separator is a comma then use this formula in cell B1:

    =VALUE(MID(A1,2,LEN(A1)-2))

    and copy down as needed.

  • Now sort your data by the B column ascending.

  • Delete column B or just clear the data in it.

Using VBA

  • Copy the code to a standard module, e.g. Module1.
  • The data is modified in place (no inserting, shifting...).
  • The decimal separator is irrelevant.
Option Explicit

Sub BubbleSortIntervalsASC()
    Const ProcName As String = "BubbleSortIntervalsASC"
    On Error GoTo ClearError

    Const FirstCellAddress As String = "A1"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    ' Reference the column range.
    Dim fCell As Range: Set fCell = ws.Range(FirstCellAddress)
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, fCell.Column).End(xlUp).Row
    Dim rCount As Long: rCount = lRow - fCell.Row + 1
    If rCount < 2 Then Exit Sub
    Dim srg As Range: Set srg = fCell.Resize(rCount)
    
    ' Write the values from the source (one-column) range to the source array.
    Dim sData As Variant: sData = srg.Value
    ' Write the same values converted to numbers to the numbers array.
    Dim nData As Variant: nData = ws.Evaluate("VALUE(SUBSTITUTE(MID(" _
        & srg.Address & ",2,LEN(" & srg.Address & ")-2),"","","".""))")
    
    Dim i As Long, j As Long
    Dim sT As String, nT As Double
    
    ' Bubble sort the numbers array
    ' and do the same 'respective' shifting in the source array.
    For i = 1 To rCount - 1
        For j = i To rCount
            If nData(i, 1) > nData(j, 1) Then
                nT = nData(i, 1): nData(i, 1) = nData(j, 1): nData(j, 1) = nT
                sT = sData(i, 1): sData(i, 1) = sData(j, 1): sData(j, 1) = sT
            End If
        Next j
    Next i

    ' Write the sorted values from the source array to the source range.
    srg.Value = sData

ProcExit:
    Exit Sub
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Sub
user_98140690
Related