The default sorting method works for alphabetic values, but does not produce valid results for numbers and dates. If you need to sort these data types, you do so using the CompareRows event of the List Box. This figure shows how code in the CompareRows event handler compares the values of adjacent rows in the Age column to get the desired sort order.
Nice, but I want to sort a list using the entries suffix: sort by string, not numbers.
I have a lot of city names that ends with:
bach,
dorf,
heim,
willer,
and probably others, etc.
This is not a one shot (in that case, I do it manually), and my testing folder have 534 entries.
Worst: the list is composed as follows: City Name - Zip # (5 digits) to allow an “eye” alphabetical search.
(I started with the zip first, and I delete that test folder and generate the current one)
That is exactly what CompareRows is designed for. Its up to you to write the code in CompareRows to “compare” them as you want… You will have to parse each row to determine the portion that comprises the comparison key… be that a string or a number
I would probably have a list of suffixes and iterate over them for each entry, creating a SortKey (eg Sachsenheim becomes heimSachsen, Hamburg becomes burgHam) and store that in the RowTag. Then I would compare the RowTags in the CompareRows event.
And while youre at it you might want to split the Zip code into its own column
Dave: that is what (more or less) the docs says *.
Markus: because of the length of the sufixes (heim: 4 chars / willer: 6 chars), I was in the dark. I love your suggestion, but I have to think a bit for a way to get my result(s).
Both: thanks for your answers.
In my internet quest, I found dark results (**) and notes about sorting numbers even when the word string appears in the op question.
Is it an unusual question ? No one ever needs that kind of feature ?
No one just recall the end of the city name and want to get the whole city name from that ?
Think What is the city name ? It have dorf in the end of it ?
** Internet can be good, but also far from help; people (me included) can be helpless; in this case, talking about numbers while the question ask about string is disturbing,
(no, not you Dave).
Lets say your list of suffixes contains burg and heim. You could have a name like Waldburgheim. You iterate for heim it becomes heimWaldburg, then you iterate with burg it becomes burgheimWald which isnt what you want. So you need to mark each entry that you already processed (you could store it in the CellTag) and only process those that havent been changed yet.
