Excel——根据另一页上列中的值进行自定义排序

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.

我想一个数据集在Sheet2基于多个标准,其中一个应该基于Sheet1列中的值粘贴g .首先我想我的数据基于升序排序第三列中的值,然后通过我的定制订单。

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

我想对表2中的数据进行排序:

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:

首先基于排名(1-n)，然后基于用户指定的col G表1中有自定义顺序的类型:

The result should then be:

结果应该是:

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

由于用户在col G中指定的值可能会改变(而且顺序不同)，所以我不能使用静态列表。通过记录宏，我得到了以下代码(使用静态列表):

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.

因此，我的问题是如何根据Sheet1 col G中的值对数据进行排序，使用循环或Excel是否知道如何将列中的所有值提取为文本字符串。

Thank you!

谢谢你！

1 个解决方案

#1

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.

因此，我的问题是如何根据Sheet1 col G中的值对数据进行排序，使用循环或Excel是否知道如何将列中的所有值提取为文本字符串。

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

转换一系列数值，例如:

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:

它将在Sheet1上将这个转换为:

To this:

#1