Changing Sorting Order

[Saighead]

Hi,

Is there a way to modify the way Excel sorts cells? This "A to Z" or "Z to A" algorithm is faulty as hell and apparently hasn't been updated since the dawn of time. It constantly sorts words in many languages in a horribly wrong order. Is it possible to create custom sorting routines?

_____
Also posted here.

[avk]

Yes possible. Attach sample file with before & after required result.

[NickyC]

you can create a custom sorting order by creating a custom list and sorting on that - eg Monday Tuesday Wednesday ...

Or, you can create a dummy variable in the column adjacent to the one you need to sort and use that. I use the latter all the time, but whether that suits you will depend a bit on what you're sorting and whether you can easily use formulas to get your data in the order you want.

[Saighead]

Yes possible. Attach sample file with before & after required result.

Well, for example, at one point I need Excel to sort words in cells using this sorting order (Estonian alphabet): a, b, d, e, f, g, h, i, j, k, l, m, n, o, p, r, s, š, z, ž, t, u, v, õ, ä, ö, ü. Can this be done without changing locale or any other dramatic steps?

[NickyC]

if you use Estonian often you could create a custom list of its alphabet and use this to create a dummy variable. Once you create it, the list will always be available – you won’t have to repeat that stage.

to create a custom list, type each letter of the alphabet in a separate cell in successive rows in a spreadsheet. Select the range with your alphabet.


To create a custom list run this macro:

Please Login or Register  to view this content.

Or follow the instructions here:

To create the dummy variable, add a blank column next to the one you want to sort and enter this formula:
=LOWER(LEFT(A1,1))
(assuming A1 is the first cell with sort data – adjust accordingly)

Copy this down the column next to every cell with a value you want to sort.

Select both the column to sort and the column with the dummy variable.

Select data > sort and under ‘order’ select ‘custom list’ then pick your alphabet from the custom lists available

Under ‘Sort by’, select the column with the dummy variable first, then select ‘add level’ and add the primary sort list.

basically what this does is to take the first letter of your entries ans sort it in the order of the Estonian alphabet you provided, then sorts by the whole word to order the rest of the list.

[NickyC]

ah - I have just realised this will sort on the first letter of each word but it may not be properly alphabetical as subsequent letters will be sorted on the roman alphabet

there is a discussion of the issue here:
https://answers.microsoft.com/en-us/...8-d2f9fe3c2c33

I'm shutting down now but will give it some thought and try to get back to you if no-one comes up with a better solution.

[NickyC]

ok I think I have worked out how to do this with a macro, though it may be slow if you have a large list.
it sorts the selected text - single column only, in the order that the characters appear in the MyAlph array (I uysed the order that you gave above).
basically what it does is

1. insert a new column to the right of the selected text and copies the text there
2. in the new column, replaces the characters in the array with strings made up of numbers indicating the character's position in the array
3. sorts the text and numbers on the numbers column
4. deletes the numbers column

Let me know if it works

Please Login or Register  to view this content.

[Saighead]

@NickyC

Thank you for the effort. But...
— "š, ž, õ, ä, ö, ü" are high-ASCII or Unicode characters. Whichever they are, VBA in Office 2010 converts them to whatever corresponds to them in the current Windows codepage or something. In my case, they are converted into Cyrillic characters (which is really weird, come to think of it, since both my Windows and my MS Office Suite are non-localized). As a result, the sorting order goes completely out of whack...
— the script sorts one column only, which would wreak havoc in my files since there's LOTS of related columns...

PS. I was hoping (in retrospect, somewhat too optimistically) that Excel had a "civilized" solution to the problem. Without generating so much garbage, without helper columns and elaborate replacement schemes. Without all that "dance with a tambourine" as they say in my language. But sadly, even after more than 3 decades on the market Excel still is a piece of junk in so many respects that it's just depressing...

[NickyC]

Hi Saighead

I don't think Excel is going to deliver what you want, at least not simply.

the macro could be adapted to sort more than one column, but I'd need to know which column contains the sort key. it also won't do a sort on more than one key. And I can't think of a way to do it without the "dance with a tambourine' I'm afraid.
I'm rather a bumbling amateur on these forums and it may be than someone with more expertise can help.

Let me know if you'd like to try an amended macro. I'm logging off now, but could look at it tomorrow

[NickyC]

Hi

this variation should allow you to sort several columns at once. If you select multiple columns it will show an input box asking which column you want to sort on. Enter eg A to sort on column A

Please Login or Register  to view this content.

[Saighead]

@NickyC

Thanks a lot! With just a bit of tweaking to make VBA see non-ASCII characters correctly the script works great.