Sorting a range by the order of another list in Excel

Question

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?

Answer 1

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