CODEWITHSUNDEEP
excelsorting
Adam Lindberg
Adam Lindberg

Reputation: 16587

Sorting a range by the order of another list in Excel

I have two tables, like this:

Table 1

  A
1 FirstPhase
2 SecondPhase
3 ThirdPhase
4 FourthPhase

Table 2

  A     B
1 Item1 FirstPhase
2 Item4 FourthPhase
3 Item2 SecondPhase
4 Item3 ThirdPhase

The result I want to achieve after sorting is:

  A     B
1 Item1 FirstPhase
2 Item2 SecondPhase
3 Item3 ThirdPhase
4 Item4 FourthPhase

How can I sort the second table by column B according to the order of column A in the first table?

Upvotes: 0

Views: 17524

Answers (1)

Robert Mearns
Robert Mearns

Reputation: 11996

The first step is to create a custom list.

  • In Excel 2007, click on the Office Icon
  • Select Excel Options - Popular - Edit custom lists
  • Click on 'Import list from cells' range button
  • Select your data and then press the Enter key
  • Click on the Import button
  • Click on OK and then OK again

To sort with a custom list.

  • Select the data to sort.
  • Click on the Home tab and the Sort & Filter
  • Select custom sort
  • Select the column to sort on
  • Then drop down on Order and select Custom List
  • Select your custom list
  • Click on OK and then OK again

In Code

Sub MakeCustomListAndSort()

Application.AddCustomList ListArray:=Sheets("Sheet1").Range("A1:A4"), ByRow:=True
'Create the custom list

ActiveWorkbook.Worksheets("Sheet2").Range("A1:B4").Sort Key1:=Range("B1:B4"), _
 Order1:=xlAscending, Header:=xlGuess, _
 OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
 Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
'Sort with latest custom list

Application.DeleteCustomList Application.CustomListCount
'Delete the latest custom list

End Sub

Upvotes: 4

Related Questions