XL: Unexpected Results Using a Custom Sort Order

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Power Macintosh, versions 5.0, 5.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

A custom sort order can be used to sort data in Microsoft Excel. A custom sort order can be used only as the first sort key. If the custom sort order is used with any key other than the first sort key, it is ignored.

RESOLUTION

To use a custom sort order for a sort key other than the first sort key, the sorts must be performed separately. For example, if the custom sort order is the second sort key, run the sort procedure twice, using one sort key each time, instead of running the sort procedure once using two sort keys. First sort the data by using only one sort key (using a sort order that is built into Microsoft Excel). Then run the sort procedure again, by using only one sort key with a custom sort order. The following example shows how to do this.

Example

1. Create a new worksheet in Microsoft Excel.

2. Click Options on the Tools menu. If you are using Microsoft Excel on the Macintosh, click Preferences on the Tools menu.

3. Click the Custom Lists tab, and then click the Add button.

4. Under List Entries, type the following list:

   North Carolina Texas Arizona Washington

   Click the Add button, to add your custom list. Click the OK button.

5. Type the following into Sheet1:

          A1: NAME       B1: LOCATION
          A2: Mary       B2: Arizona
          A3: Joe        B3: Washington
          A4: John       B4: Texas
          A5: Paul       B5: Texas
          A6: Sue        B6: North Carolina
          A7: Don        B7: North Carolina
          A8: Alice      B8: Arizona
   
    6  Select the data range A1:B8.
   
   

6. Click Sort on the Data menu.

7. Make sure that Header Row is selected in the "My List Has" field.

8. In the Sort By field, select NAME and select Ascending. Click OK. The list has now been sorted once using the sort order built into Microsoft Excel.

9. Reselect the data range A1:B8.

10. Click Sort on the Data Menu.

11. Make sure that Header Row is selected in the "My List Has" field.

12. In the Sort By field, select LOCATION.

13. Click the Options button. Select the custom list (that you added at the beginning of the example) from the "First Key Sort Order" box. Click OK.

14. In the Sort box, click OK.

STATUS

This behavior is by design. The custom sort order is meant to be used with the first sort key only.

MORE INFORMATION

Macro Example

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/supportnet/refguide/

   Sub Custom_Sorting()
       ' This runs the first sort proceudre, using the "normal" sort order.
       Range("A1:B8").Sort Key1:=Range("A2"), Order1:=xlAscending, _
          Header:= xlYes, MatchCase:=False, Orientation:=xlTopToBottom
       ' This runs the second sort proceudre, using a custom sort order.
       Range("A1:B8").Sort Key1:=Range("B2"), Order1:=xlAscending, _
          Header:= xlYes, OrderCustom:=6, MatchCase:=False, Orientation:= _
           xlTopToBottom
   End Sub

1. From a worksheet containing data, click Sort on the Data menu.

2. Click on the Options button.

3. Click the arrow to the right of the "First Key Sort Order" box.

4. Count the number of lists in the dropdown box. Determine what position your custom list is in in the dropdown list box. That is the index number for your custom list. The first five lists in the dropdown list box are built in.

For more information about Using a Custom Sort Order, click the Search button in Help and type:

   custom sort order