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

Question

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:

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:

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!

fyi you might be interested in my late answer using methods prior to Excel 365 to solve your question. — T.M., Oct 16 '20 at 13:34

score 5 · Accepted Answer · answered Oct 14 '20 at 18:05

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))

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

score 2 · Answer 2 · answered Oct 16 '20 at 13:23

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

Application.Match together with a bubble sort routine (to sort a 1-based 2-dim datafield array containing found indices) and
a rather unknown way to rearrange arrays via Application.Index() function - c.f. ►Some pecularities of the Application.Index() function

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

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

2 Answers2