Dynamic sorting with Excel, VBA, and Python

A spreadsheet with User Defined Functions (UDFs) to dynamically sort a range of data has previously been presented here and here.

I have now modified the Python version of the UDF for improved functionality, added a second Python function, and added an example of how a dynamic sort can be accomplished without programming using the Rank() function.  The revised spreadsheet, including full open-source code, can be downloaded from: Sortfunc.zip.

Sorting data in Excel can be accomplished most easily (since 2007) by inserting the data as a table.  The data can then be sorted simply by clicking on the header of the sort column:


Table sorted on Column A


If you need a table that will automatically update when new data is entered, things are not so simple however.  The screen shot below shows the procedure using the built-in Rank() function.:


This procedure requires 4 dummy columns to generate the required row index values, which are used in conjunction with the Index() or Offset() functions to return the data.  Also note that this procedure cannot deal with two or more rows with exactly equal sort values, so the values are adjusted by subtracting different very small values from each row.

For situations where VBA is available, the VBA UDF shown below makes the whole procedure much easier and simpler: py_sort3

The only disadvantage of the VBA routine is that it only allows for one sort column.  This has been fixed in the revised Python sort function shown below:


The options range, specifying sort columns and sort directions, may be any number of columns wide.

The code for this function is shown below:

from operator import itemgetter, attrgetter

@xl_func("var SortRange, var SortCol: var")
def py_Sort(SortRange,SortCol):
    if SortCol is None:
        return sorted(SortRange)
        numsortrows = 0
        if type(SortCol) is list:
            numsortcols = len(SortCol[0])
            numsortrows = len(SortCol)
            x = int(SortCol[0][0])-1
            numsortcols = 1
            x = int(SortCol)-1
    sortrev = False
    for i in range(numsortcols-1,-1,-1):
        if numsortcols != 1: x = int(SortCol[0][i])-1
        if numsortrows > 1:
            if SortCol[1][i] is None:
                sortrev = False
                sortrev = SortCol[1][i]
        SortRange = sorted(SortRange, key=itemgetter(x), reverse = sortrev)
    return SortRange

A second Python function has been added, using the numpy argsort function for improved performance where there is only one sort column, and for use in other VBA and Python routines.  Note that this function returns the row offset for the sorted list, which can then be used with the Excel Index() or Offset() functions:


The code for this function is:

@xl_func("numpy_array SortRange, bool RevSort: numpy_array")
def py_ArgSort(SortRange, RevSort):
    if RevSort is None: RevSort = False
    sortind  = np.argsort(SortRange,0)
    if RevSort == True:
        indlen = sortind.shape[0]
        revind = np.zeros((indlen,1))
        indlen = indlen-1
        for i in range(0, indlen+1):
            revind[i] = sortind[indlen-i]
        return revind
    return sortind

For more details of using array functions, see the Using Array Functions and UDFs page.

For more details of installing and running Python from Excel, using the Pyxll add-in, see Installing Python, Scipy and Pyxll.

This entry was posted in Excel, Link to Python, NumPy and SciPy, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s