How can I sort my data in alphabetic order?

Custom sort on second sort field

• QUESTION: I have three columns of data that need to be sorted. The first column is a list of names which needs to be sorted in ascending alphabetical order. The second column is an ID number to be sorted in ascending numeric order. The third column contains one of the values (NOV, DEC, JAN, FEB, MAR, OTHER). This column needs to be sorted in ascending order with the first (lowest) value being user-specified. For instance, if a user specifies DEC, then this second column should have DEC followed by JAN, then by FEB, etc. with NOV at the bottom of the list. I have created a macro to define the custom sort list depending on the user-specified month. I then have a sort statement in the macro to use this custom order. However, the resulting sort does not seem to be working on the third column. The third column is always sorted with OCT as the lowest value. Here is the VBA sort statement: Selection.Sort Key1:=Range(Cells(Temp_Start_Row, Temp_Agent_Name), Cells(Temp_Start_Row, Temp_Agent_Name)), Order1:=xlAscending, _ Key2:=Range(Cells(Temp_Start_Row, Temp_Agent_ID), Cells(Temp_Start_Row, Temp_Agent_ID)), Order2:=xlAscending, _ Key3:=Range(Cells(Temp_Start_Row, Temp_Renew_Mo), Cells(Temp_Start_Row, Temp_Renew_Mo)), Order3:=xlDescending, _ Header:=xlYes, OrderCustom:=Custom_List_Count, MatchCase:=False, Orientation:=xlTopToBot_ DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortNormal Is there a way to specify to use the custom sort only for the third column? ANSWER: You don't say what version ofyou have, but you are using the sort method which has existed for many many versions of Excel. A new sort object was added in Excel 2007 and you can do a custom list in that verion and later But that method doesn't use the code you show. for your code the custom sort has to be on the first order. You can overcome this by first sorting your data by only the custom sort. then do a second sort using your first two keys. uses a persistent sort, so the order in the first sort should be maintained within the second sort. (i.e. it should be in the order you want. Custom_List_Count = 6 Selection.Sort Key1:=Range(Cells(Temp_Start_Row, Temp_Renew_Mo), Cells(Temp_Start_Row, Temp_Renew_Mo)), Order1:=xlDescending, _ Header:=xlYes, OrderCustom:=Custom_List_Count, MatchCase:=False, Orientation:=xlTopToBot_ DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortNormal Selection.Sort Key1:=Range(Cells(Temp_Start_Row, Temp_Agent_Name), Cells(Temp_Start_Row, Temp_Agent_Name)), Order1:=xlAscending, _ Key2:=Range(Cells(Temp_Start_Row, Temp_Agent_ID), Cells(Temp_Start_Row, Temp_Agent_ID)), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBot_ DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortNormal pay attention to this article on how you specify the custom list number OrderCustom:=1 means normal. No custom sort http://support.microsoft.com/kb/213625 very very important. I tested this approach in2000 and it worked fine. I didn't test the exact code above because I don't have all your variables defined and so forth but hopefully you will find quick success. ---------- FOLLOW-UP ---------- QUESTION: Tom - I'm using2003. If I change the possible values in the custom sort to NOV, DEC, JAN, Betwee FEB-SEP, OCT then the sort does not turn out properly. I've stepped through both of the sort statements, and the first sort (using the custom sort) does not occur properly. When I try the sorts manually - not using the macro - after manually entering the custom sort order into Tools | Options, the sorts occur properly. I can't figure this out. Do you have any suggestions? Also, I don't think that I still need to retain the DataOption2 and DataOption3 statements in the first sort statement along with not needing the DataOption3 statement in the second sort statement. Is my understanding correct?

• Answer:

  I can't advise you on the the Dataoption arguments because they are specific to your data and what you are trying to achieve. Those didn't exist in2000 where I tested it and they are optional in later versions. I don't think I have ever done a sort where I used them, but as I said, they are data and environment specific. I would suggest you turn on the macro recorder while you do the manual sorts that work. That should give you the proper code. This is the code I used and it worked fine for me. Sub SortUsingCustomSort() Selection.Sort Key1:=Range("C2"), _ Order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=2, _ MatchCase:=False, _ Orientation:=xlTopToBot Selection.Sort Key1:=Range("A2"), _ Order1:=xlAscending, _ Key2:=Range("B2"), _ Order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBotEnd Sub I don't know how you are defining your list and so forth. I also don't know what part you say is not working or why you say it is not working. I used a predefined list: Sun, Mon, Tue, etc I tested it with both dates in column C and with the names of the days hard coded in. Both worked fine for me. If you would like me to look at your workbook, you can send it to or if you want me to send you my workbook with the code, contact me at the above address. (just recall that I don't have xl2003 readily available - I have xl2000 and xl2007. I can get to a copy of xl2003 if that is necessary but that would take a while).