XL: Unexpected Results Using a Custom Sort Order

ID: Q151346

The information in this article applies to:

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.


The data has now been sorted so that the second sort key is using a custom sort order.


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 professionals 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/support/supportnet/refguide/

   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


NOTE: In the second sort procedure, the custom sort order is specified using the value 6. The value 6 is the index number given to the custom list (in the Custom List box under the Options on the Tools menu). To determine which index number has been assigned to your custom list, follow these steps:

  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

Last Reviewed: July 23, 1999