1.    20 Sep 2015 #1

    EXCEL sort sequence

    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.

      My ComputerSystem Spec
  2.    20 Sep 2015 #2

    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.



      My ComputerSystem Spec
  3.    21 Sep 2015 #3

    Hi there


    that worked a treat (option 1).

      My ComputerSystem Spec


Similar Threads
Thread Forum
How to set boot priority sequence permanently on Lenovo G 50 70
My laptop is running on Window10 efficiently. Sometimes in order to check the DVDs and USBs bootability of newly created OS or Recovery Disk etc I have to select the relative option in the the bios. But on Lenovo G 50 70 the selected option is not...
Installation and Upgrade
How do I sort my favorites in Windows 10 Edge?
How do I sort my favorites in Windows 10 Edge? Preserve the Order of Edge Favorites When Transferring to Another web browsers?
Browsers and Email
Solved All Apps - How to sort by Category?
In Windows 8.1, when I go to the start screen and show the all apps, I sort the apps by Category (instead of alphabetical). I also select an option to show Desktop apps first. Is it not possible to do this in Windows 10? I am using Windows 10...
General Support
Solved How to sort documents
I have copied my documents from win 7 to win 10 build 9879 and they are reversed in order. For example zip codes is listed first, while acronis is listed last on the page. There are about 50 total docs. on the page. Does anyone know how to...
General Support
Disable Taskbar Transparency (Sort of...)
Okay, so you've set your favorite desktop wallpaper and you've got all your windows border colors and Start Menu colors set in Windows Technical Preview but that darn taskbar doesn't match! Furthermore, you'd rather not use a third party program to...
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 20:44.
Find Us
Twitter Facebook Google+ Ten Forums iOS App Ten Forums Android App

Windows 10 Forums