VBA Merge sort

  • I don't know if this will help anybody, nor can I be sure this code is bullet proof, but what I can say is that I use it an awful lot to quickly sort arrays in VBA and I haven't broken it yet. Could be useful?
    Nb. this mutates the original array so make a copy of your array first if you want one with the original order in place.


  • Hmmmm... wish I had seen this yesterday... LOL. I’ll try it out later.

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Awesome sauce... very very quick for me on a 2D x 1 column sort with numbers. over 40k rows.. suggestion for improvement... add another optional parameter for ascending or descending... not sure how badly that would screw up the code. Or maybe include instructions to convert between one and the other. I guess its a case of changing all < with > ? :) Thanks mate.


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • There is definitely a more efficient way of doing this, but here's a quick and dirty version with a descending flag as the last optional argument to sort an array in descending order. Just set it to true to take effect.


  • Made some massive changes on this to try and make it slightly more intelligent.


    There is now an optional flag for ascending or descending sorts (unfortunately this is still global as I haven't had time to elegantly do it on a per sort key basis) and I've also fixed a bug if an array contained objects rather than just simple variable types. I've also changed the way the algorithm treats numbers within a text string so "10 Blah" would now sort after "2 Blah" (see example Sub below for what I mean).


    Anyway, here's the updated sort code: sortArray.txt


    And heres a little demo of what I mean about how Strings containing numbers are handled (you can use the autofilter to see what the default sort would have been vs. what the sort code does):


  • This is simply amazing, it helps me a lot. Thank you very much for sharing!!!


    Can I ask if it's possible to move the blank items in the 2D-array ("") to the end instead of the very beginning?


    Either way, thank you for this great job!!!

  • Hey trunten,


    I've humbled over your very useful solution (Thank you!) and I may suggest to you, that you can indicate the sortorder by using negative/positive values within the sortkeys array. For example, if there is an twodimensional array with 4 columns, the user may give the parameter like array(3,-2,1), which means that the columns 3 and 1 will be ascending sorted and column 2 will be descending. I've done this before in a completely different case, but I thought it might be a nice way to solve this.


    Unfortunately I couldn't figure out, where in your code you iterate through the columns, so I can't make a code example (I'm non professional, just learning by doing).


    Excuse my English, best regards from good old germany,

    Sergeij