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:

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)
    else:
        numsortrows = 0
        if type(SortCol) is list:
            numsortcols = len(SortCol[0])
            numsortrows = len(SortCol)
            x = int(SortCol[0][0])-1
        else:
            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
            else:
                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.