Windows 10: EXCEL sort sequence Solved

  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


Related Threads
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...
How do I sort my favorites in Windows 10 Edge? Preserve the Order of Edge Favorites When Transferring to Another web browsers?
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...
Solved How to sort documents in General Support
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...
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 01:41.
Find Us