NewBee
NewBee

Reputation: 1040

Order one column based on the order of another column-- excel

I am trying to order column A, based on the order of column B.

A similar question was asked here, but the solution is not working as expected. Order a column based on another column

Here is an example of my data:

enter image description here

This is the VBA that I am running:

Sub sort_a_b()

Application.ScreenUpdating = False
Range("A1:B12").Copy Destination:=Range("G1:H12")
Range("G1:H12").Sort Key1:=Range("H1:H12"), Order1:=xlAscending
Range("G1:G12").Copy Destination:=Range("A1:A12")
Range("G1:H12").Clear
Application.ScreenUpdating = True

End Sub

This is the result I see when I run it:

enter image description here

I am not sure what is going on. I expect to see May, Aug, Nov, Dec based on the order of column B, but it is not working that way. Please note that my actual list is 500 variables long!

Any suggestions are greatly appreciated--either manual or macro. Thankyou!

Upvotes: 2

Views: 2009

Answers (2)

T.M.
T.M.

Reputation: 9948

Alternative not using Excel 365

"If you have access to Excel 365 and SORTBY formula, then you're in luck."

In addition to the valid answer above a late post intending to help the "not so lucky ones" to solve the question by methods prior to the dynamic Excel 365 features:

Main procedure ReorderBy

using

Sub ReorderBy(data, pattern)                                    ' data: Aug,Nov,Dec,May
    With Application
        'a) fill 2-dim array with pattern indices and sort it
        Dim indices:   indices = .Match(data, pattern, 0)       ' ~> 8,11,12,5
        BubbleSort2Dim indices                                  ' ~> 5,8,11,12
        'b) reorder data based on pattern indices
        data = Application.Index(pattern, indices, Array(1))    ' ~> May,Aug,Nov,Dec
    End With
End Sub
Sub BubbleSort2Dim(arr, Optional colNo As Long = 1)
    Dim cnt As Long, nxt As Long, temp
    For cnt = LBound(arr) To UBound(arr) - 1
        For nxt = cnt + 1 To UBound(arr)
            If arr(cnt, colNo) > arr(nxt, colNo) Then
                temp = arr(cnt, colNo)
                arr(cnt, colNo) = arr(nxt, colNo)
                arr(nxt, colNo) = temp
            End If
        Next nxt
    Next cnt
End Sub

Example Call

using

  • [1] a help function getData() to get column data and eventually calling
  • [2] the main routine ReorderBy:

ad [1]) Note that the worksheet argument in getData() is passed here as CodeName reference (e.g. Sheet1) to the current project.

Sub ExampleCall()
'[1] define data and pattern arrays
    Dim data:    data = getData(Sheet1, "A")    '[A2:A5]
    Dim pattern: pattern = getData(Sheet1, "B") '[B2].Resize(12))
'[2] reorder data
    ReorderBy data, pattern                     ' << call main routine (By Ref arguments!)
'[3] write results to any target
    Sheet1.Range("D2").Resize(UBound(data), 1) = data
End Sub
Function getData(ws As Worksheet, ByVal col, Optional ByVal StartRow& = 2) As Variant()
    ' Purpose: assign column data to variant array
    If IsNumeric(col) Then col = Split(ws.Cells(1, col).Address, "$")(1)
    Dim LastRow As Long
    LastRow = ws.Range(col & Rows.Count).End(xlUp).Row
    getData = ws.Range(col & StartRow & ":" & col & LastRow).Value2
End Function

Upvotes: 2

Justyna MK
Justyna MK

Reputation: 3563

If you have access to Excel 365 and SORTBY formula, then you're in luck. As you can see, the formula solution is quite straightforward -

=SORTBY(A1:A4,MATCH(A1:A4,$B$1:$B$12,0))

enter image description here

The middle argument, MATCH, is basically assigning the numeric order of a given month -

enter image description here

Upvotes: 5

Related Questions