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

One Response to “Dynamic Sorting in EXCEL”

Comments (1)
  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.

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>

© 2014 Spreadsheet Advice Suffusion theme by Sayontan Sinha