Modify Excel's Sorting Procedure

[Guest]

Is there a way that when I go to data>sort 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

 

[Rob van Gelder]

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.

 

[Guest]

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

 

[Rob van Gelder]

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.

 

[Peter T]

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