Dynamic Sorting in EXCEL

Posted by Hindemith on April 15, 2011 at 22:52 EXCEL, VBA
Apr 152011

Sorting is numerically costly and this can be felt when the arrays to sort are large. The attached code is based on the QuickSort algorithm, and does the job very fast -- a very useful routine to have in one's arsenal of VBA codes.In fact there are four VBA routines that might be useful:

  • sortarray: sorts a (2-dimensional) array taken as a variant variable, i.e. it can sort numbers, strings or even a mixture of the two
  • sortvector: same as sortarray but for a 1-dimensional array
  • sortarray_num: same as sortarray but only accepts numbers (i.e. Double variables). This is much more memory efficient and might make a big difference for large arrays
  • sortvector_num: same as sortarray_num, but for a 1-dimensional array

Sortarray (and its variants) allows to choose, whether you would like to sort the array in ascending or descending order, and also lets you sort by up to 5 freely chosen columns.

I went a step further and also coded an EXCEL worksheet-function that allows to dynamically sort an array -- a feature that is still missing in EXCEL. The worksheet-function is called sortarray_wsf and lets you choose

  • whether you would like to sort in ascending or descending order
  • whether you would like to sort by columns or rows
  • by which column (or row) you would like to sort -- you can specify up to 5 sorting layers

This function sortarray_wsf is substantially faster than other constructs for dynamic sorting (like copied LARGE or SMALL functions, a method, which essentially sorts the array as many times as there are rows)

Attached below is a workbook with all the described VBA routines. No warranty whatsoever is accepted, you are using this entirely at your own risk:

Dynamic_Sorting_v1

3 Responses to “Dynamic Sorting in EXCEL”

Comments (3)
  1. This is more or less what I need. But it would be significantly easier to use, if there were some help text when writing the function in Excel.

    E.g. if I use a standard Excel function like vlookup, there are some help text, so - as I type - it looks like:
    VLOOKUP(lookup_value, table_array; col_index_num; [range_lookup])

    ...and when I come to [range_lookup] it gives me options with explanations.

  2. Could you please tell me how to re-size the output field? I don't know VB at all, but I am trying to expand the list to be 200 potential rows long and I can't get the display to work, although I seem to have amended the source fields.

  3. Hi Simon,

    I suspect you are trying to re-size the output field while the array formula there is intact, which is not possible.

    The EXCEL worksheet function you see implemented in the Dynamic_Sorting workbook is called "sortarray_wsf" and it is an array function that is written into the entire output range, which you cannot re-size for that reason. What you should do is:

    1. Start from scratch, e.g. use a new worksheet in the Dynamic_Sorting workbook.

    2. Write your data in the source range (say B14:T213 for 200 rows and some columns)

    3. Select a result range of equal size

    4. Write "=sortarray_wsf(B14:T213)" into the formula box, add some optional arguments if you wish

    5. Press CTRL SHIFT ENTER instead of just ENTER to tell excel it should expect an array formula.

    That should do the trick. You might also have a look how the array formula MINV is used in EXCEL. This is also a formula that takes a matrix as input and outputs a matrix of the same size and is used in the same way as sortarray_wsf. See e.g. here: http://www.excelfunctions.net/Excel-Minverse-Function.html

Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

© 2015 Spreadsheet Advice Suffusion theme by Sayontan Sinha