Hi there
I'm trying to create a list of Movies in my DB in alphabetical order with folders A,B,C etc.
Is there a way to store for example "The Day the earth Stood Still - 1951" in D ignoring the word "THE" in the sort sequence if it's the first word of the film title. I also don't want to store the title as for example "Day the Earth stood still The"
Should be simple but I can't seem to construct a decent EXCEL formula that does this for me.
Cheers
jimbo
Hi Jimbo,
this custom sort is possible....ofcourse! :) Let me explain two easy ways to do this.
1) Create an extra column and insert a formula like "IF(LEFT(xx;3)="The";MID(xx;4;LEN(xx));xx)
Now simply sort the database on this column.
It is nice to hide this column and write a litte bit VBA code to do the sort :)
2) Do the entire thing from VBA. The fastest way is to load the entire database into a collection and then do the sort in the memory and print the results.
However...if you are not used to working with collections this is very difficult. So another option is to loop through the database using the bubble algorithme (check internet for the code) and ofcourse don't forget to tell the code that, if the first word is "The", it sometime have to skip some letters.
Method 1 is the easiest. Method 2 is the most beautiful option.
Goodluck!
Greetz,
Rover
Hi there
thanks
that worked a treat (option 1).
Cheers
jimbo
Quote