Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Excel's Sorting Procedure
Is there a way that when I go to datasort and choose a particular column to
sort by (in my case column E) It will sort using my own instructions? (ie. I want to be able to sort non-alphabetically, but by a predefined order). It would also be nice to use this whether it is the 1st, 2nd or 3rd option for sorting. Thanks Vinny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Excel's Sorting Procedure
You can...
From Tools | Options | Custom Lists Create your own list. Select your range. The from Data | Sort | Options... select your new list from the First Key Sort Order dropdown. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Vinny" wrote in message ... Is there a way that when I go to datasort and choose a particular column to sort by (in my case column E) It will sort using my own instructions? (ie. I want to be able to sort non-alphabetically, but by a predefined order). It would also be nice to use this whether it is the 1st, 2nd or 3rd option for sorting. Thanks Vinny |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Excel's Sorting Procedure
Thanks for the help Rob,
But not exactly what I was looking for. Let me rephrase. I actually want to sort a list by character, but in non alphabetical order (change the order to a, b, g, d, e, z, h, q, i, k, l, m, n, x, o ... etc; so the leter combination 'ago' would appear before 'ado', for example). I wouldn't, therefore, be able to create a list of all the leter combinations. I have created a procedure that will do this for me, but it works when i press ctrl+s, and i was wondering if there was a way to append it to the way that excel already sorts, so that when I chose to sort by column E (the column with these words in it) it will sort by my "alphabet" instead. Doing this would alow me to sort by column E, plus two other columns (i could modify my existing macro to sort by the two other columns, but they often change, and sometimes I want to be able to sort by another column first then column E). Thanks again, Vinny |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Excel's Sorting Procedure
There isn't an event for "On_Sort", so you cannot override it the nice way.
The other (not so nice) way is to replace the Sort menu items and shortcut keys with a custom macro. I cant be more help I'm afraid. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Vinny" wrote in message ... Thanks for the help Rob, But not exactly what I was looking for. Let me rephrase. I actually want to sort a list by character, but in non alphabetical order (change the order to a, b, g, d, e, z, h, q, i, k, l, m, n, x, o ... etc; so the leter combination 'ago' would appear before 'ado', for example). I wouldn't, therefore, be able to create a list of all the leter combinations. I have created a procedure that will do this for me, but it works when i press ctrl+s, and i was wondering if there was a way to append it to the way that excel already sorts, so that when I chose to sort by column E (the column with these words in it) it will sort by my "alphabet" instead. Doing this would alow me to sort by column E, plus two other columns (i could modify my existing macro to sort by the two other columns, but they often change, and sometimes I want to be able to sort by another column first then column E). Thanks again, Vinny |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify Excel's Sorting Procedure
Vinny,
As Rob says I can't see any other way other than with a macro to do everything, or a bit of help from a UDF. Various approaches according to overall needs. Try following UDF to insert a column of helper cells. Sort on that together with the original column. Function DisOrder(vInput) As String Dim vArr, n As Long, c As Long Dim sIn As String, sOut As String ' ("a", "b", "g", "d", "e", "z", "h", "q", _ ' "i", "k", "l", "m", "n", "x", "o", "c", "f", _ ' "j", "p", "r", "s", "t", "u", "v", "w", "y") vArr = Array("a", "b", "p", "d", "e", "q", "c", "g", _ "i", "r", "j", "k", "l", "m", "o", "s", "h", _ "t", "u", "v", "w", "x", "y", "n", "z", "f") sIn = LCase(vInput) For n = 1 To Len(sIn) c = Asc(Mid(sIn, n, 1)) If c 96 And c < 123 Then sOut = sOut & vArr(c - 97) Else sOut = sOut & Chr(c) End If Next DisOrder = sOut End Function You only gave the order of 15 out of 26 letters so I've guessed the others, adjust the array as required. Regards, Peter T "Rob van Gelder" wrote in message ... There isn't an event for "On_Sort", so you cannot override it the nice way. The other (not so nice) way is to replace the Sort menu items and shortcut keys with a custom macro. I cant be more help I'm afraid. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Vinny" wrote in message ... Thanks for the help Rob, But not exactly what I was looking for. Let me rephrase. I actually want to sort a list by character, but in non alphabetical order (change the order to a, b, g, d, e, z, h, q, i, k, l, m, n, x, o ... etc; so the leter combination 'ago' would appear before 'ado', for example). I wouldn't, therefore, be able to create a list of all the leter combinations. I have created a procedure that will do this for me, but it works when i press ctrl+s, and i was wondering if there was a way to append it to the way that excel already sorts, so that when I chose to sort by column E (the column with these words in it) it will sort by my "alphabet" instead. Doing this would alow me to sort by column E, plus two other columns (i could modify my existing macro to sort by the two other columns, but they often change, and sometimes I want to be able to sort by another column first then column E). Thanks again, Vinny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop a Procedure from another procedure | Excel Discussion (Misc queries) | |||
Calling a procedure in a procedure | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming |