Microsoft KB Archive/151346

= Unexpected Results Using a Custom Sort Order =

Article ID: 151346

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q151346



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
 Create a new worksheet in Microsoft Excel. On the Tools menu, click Options. If you are using Microsoft Excel on the Macintosh, click Preferences on the Tools menu. Click the Custom Lists tab, and then click Add.  Under List Entries, type the following list:

      North Carolina Texas Arizona Washington Click the Add button, to add your custom list. Click OK.   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  Select the data range A1:B8.</li> Click Sort on the Data menu.</li> Under My List Has, make sure that Header Row is selected.</li> 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.</li> Reselect the data range A1:B8.</li> Click Sort on the Data Menu.</li> Under My List Has, make sure that Header Row is selected.</li> In the Sort By list, click LOCATION.</li> Click Options. Select the custom list (that you added at the beginning of the example) from the "First Key Sort Order" box. Click OK.</li> In the Sort box, click OK.</li></ol>

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

<div class="moreinformation_section">

Macro Example
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. 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.

<div class="references_section">