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 the Macintosh
• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a

SYMPTOMS

You can use a custom sort order to sort data in Microsoft Excel. You can only use a custom sort order as the first sort key. If you use the custom sort order with any key other than the first sort key, it is ignored.

RESOLUTION

The custom sort order is meant to be used with the first sort key. 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 ne 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. On the Tools menu, click Options. If you are using Microsoft Excel on the Macintosh, click Preferences on the Tools menu.



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



4. Under List Entries, type the following list:
   
   

   
          North Carolina
          Texas
          Arizona
          Washington 

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



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.



7. Click Sort on the Data menu.



8. Under My List Has, make sure that Header Row is selected.



9. In the Sort By list, click NAME and click Ascending. Click OK. Microsoft Excel has now sorted the list once using the sort order built into it.



10. Reselect the data range A1:B8.



11. Click Sort on the Data Menu.



12. Under My List Has, make sure that Header Row is selected.



13. In the Sort By list, click LOCATION.



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



15. In the Sort box, click OK.

MORE INFORMATION

Macro Example

http://www.microsoft.com/support/supportnet/overview/overview.asp

   Sub Custom_Sorting()
      ' This runs the first sort procedure, 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 Options.



3. Click the arrow next to "First Key Sort Order," and count the number of lists in the drop-down box. Determine what position your custom list is in the drop-down list box. That is the index number for your custom list. The first five lists in the drop-down list box are built in.

REFERENCES

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

   custom sort order 

Additional query words:

Keywords : xllist
Version : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbhowto kbprb