CODEWITHSUNDEEP
arraysvbaexcelsorting
Pierre
Pierre

Reputation: 741

Excel - Custom sort by values in column on another sheet

I am trying to sort a data set in Sheet2 based on multiple criteria where one of them should be based on the values pasted in Sheet1 column G. So first I would like to sort my data based on the ascending order of the values in the third column and then by my custom order.

I would like to sort the following data in Sheet 2:

pic1

first based on the Rank (1-n) and then based on the Type with a custom order in col G Sheet 1 specified by the user:

pic2

The result should then be:

pic3

As the user specified values in col G may change (and also be of different order) I cannot use a static list. By recording macros I have come up with the following code (which uses a static list): 

Columns("A:C").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("C2:C9999") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B9999") _
 , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Type1,Type2,Type3" _
, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:C9999")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

So my question is how to sort the data based on the values in Sheet1 col G by using either a loop or if Excel knows how to extract all values in a column as a text string.

Thank you!

Upvotes: 1

Views: 1116

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

So my question is how to sort the data based on the values in Sheet1 col G by using either a loop or if Excel knows how to extract all values in a column as a text string.

To convert a range of values e.g. on Sheet2:

enter image description here

To a comma-delimited string, you can use:

Join(WorksheetFunction.Transpose(ThisWorkbook.Worksheets("Sheet2").Range("A2:A4")), ",")

Which will return:

Type1,Type2,Type3

So, your code might be:

Option Explicit

Sub Test()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rngToSort As Range, rngOrderType As Range

    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set rngToSort = ws1.Range("A1:C5")
    Set rngOrderType = ws2.Range("A2:A4")

    With ws1.Sort.SortFields
        .Clear
        .Add Key:=rngToSort.Columns(3), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending
        .Add Key:=rngOrderType.Columns(2), _
            SortOn:=xlSortOnValues, _
            CustomOrder:=Join(WorksheetFunction.Transpose(rngOrderType), ",")
    End With

    With ws1.Sort
        .SetRange rngToSort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With

End Sub

Which will convert this on Sheet1 from this:

enter image description here

To this:

enter image description here

Upvotes: 1

Related Questions