Listbox: sorting a column by its “suffix” ?

The LR (my) states:

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:
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 you’re 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,:frowning:
(no, not you Dave).

Just be careful when you iterate over the list.

Let’s 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 isn’t 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 haven’t been changed yet.

Markus: good catch !

BTW: below is an idea of the main window:

The text below is not definitive… but shows the trouble.

Ha. My ancestors were from Waldburg :slight_smile:

They had their “Heim” there … TA-DA :wink: