CODEWITHSUNDEEP
excelsortingvba
Alex.Becks
Alex.Becks

Reputation: 11

Sorting excel table based on 1 column

I'm working on a project that has a data table with 10 columns and a variable amount of rows. The first column 'A' title is "Case" and the inputs in this column are unsorted. I have column 'X' which has the inputs in the right order. Columns 'B' thru 'J' are based on column 'A'. I'm looking for a way to sort column 'A' based on column 'X'.

input looks like this for example : 

C4
C1 
C11
W36
W39
C21

And it needs to look like this:

W36
W39
C1
C4
C11
C21

Any help with the vba code would be useful. I'm fairly new to programming.

Upvotes: 1

Views: 174

Answers (2)

user4039065
user4039065

Reputation:

You are going to have to bring the table values into an array, perform a custom in-memory sort by splitting the alphabetic characters from the numeric characters in column A (the former descending, the latter ascending).

Sub sortVals()
    Dim srt As Variant, tmp As Variant
    Dim x As Long, a As Long, s As Long

    With Worksheets("Sheet2")
        With .Cells(1, 1).CurrentRegion
            'grab values from primary key column
            With .Resize(.Rows.Count - 1, 10).Offset(1, 0)
                srt = .Cells.Value2
            End With

            'perform custom sort on array
            For s = LBound(srt, 1) + 1 To UBound(srt, 1)
                For a = LBound(srt, 1) To UBound(srt, 1)
                    If Asc(srt(s, 1)) > Asc(srt(a, 1)) Or _
                       Int(Mid(srt(s, 1), 2)) < Int(Mid(srt(a, 1), 2)) Then
                        ReDim tmp(1 To 1, LBound(srt, 2) To UBound(srt, 2))
                        For x = LBound(tmp, 2) To UBound(tmp, 2)
                            tmp(1, x) = srt(a, x)
                            srt(a, x) = srt(s, x)
                            srt(s, x) = tmp(1, x)
                        Next x
                    End If
                Next a
            Next s

            'dump the values back into the worksheet
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                .Cells(1).Resize(UBound(srt, 1), UBound(srt, 2)) = srt
            End With

        End With
    End With
End Sub

      custom_sort_before
                    Sample data before custom sorting routine

      custom_sort_after
                    Sample data after custom sorting routine

Upvotes: 1

Chris
Chris

Reputation: 185

Try this:

Sub SortData()

Range("A:X").Select 'Your Data range
ActiveWorkbook.Worksheets("Your sheet name").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Your sheet name").Sort.SortFields.Add Key:=Range("Sorting range IE: X:X"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Your sheet name").Sort
    .SetRange Range("A:X") 'Your data range
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

You need to substitute the ranges with your own.

Upvotes: 0

Related Questions