More Sort Functions

I have recently added three new sorting User Defined Functions (UDFs) to the SortFunc-py spreadsheet, and added a new option to the SortV function.  The new file may be downloaded from, including full open source code.

The first new function is  SortVI, which works in the same way as SortV, but adds an additional index column to the output array, listing the position of each row in the original unsorted array (click any image for full size view):


The other two new functions allow sorting of rows, rather than columns.  The built-in Excel sort routine allows for sorting in either a vertical or horizontal direction (as shown below), but the new UDFs provide a dynamic sort, when used on the spreadsheet, and can also be used on VBA arrays.


The SortH function provides the same functionality as SortV, except that the data is sorted by rows, rather than columns.  Both functions have a new NumCols (or NumRows) option, that allows only part of the input range to be sorted.


The final new function is SortRows, which sorts each row in an array independently, as shown below.  It also provides two additional arguments that allow a number of columns at the start of the range to be unsorted, and allows for a column to be specified with the number of entries to be sorted in each row.  In the example below Column B specifies the number of rows to the right to be sorted, so that the cells containing a zero are not included in the sort.



Posted in Arrays, Excel, UDFs, VBA | Tagged , , , , , | Leave a comment

ConBeamU Update

Recently a comment pointed out that if the ConBeam, or ConBeamU, user defined functions were used with a single span then the “Out” argument was ignored, so it was not possible to return support reactions.  This has now been fixed and the new version can be downloaded from: ConBeamU


For more details of ConBeamU and the related functions see:
Continuous Beam Spreadsheet – with Units

Posted in Beam Bending, Excel, Frame Analysis, Newton, UDFs, VBA | Tagged , , , | Leave a comment

“Yields circularity when preceded by its quoation” …

yields circularity when preceded by its quotation.

Today’s post takes us on a little circular tour of the internet, starting with:

Grumpy Old Programmer (Mike Woodhouse) who returns from a long holiday from blogging to publish a nice little routine to generate a circular optical illusion:


He refers to the (very compact) code as being “golfed”, with a link to Programming Puzzles and Golf code, where I was introduced to the idea of a computer programming “quine”, that is computer code, which when run reproduces itself in full.  A very brief example in Python is shown below (line 1 is the code, the second line the output).

Python quine

But I can do better than that, using the “Classic” Lotus 123 macro script.  If we enter in cell B1:  /C~{D}~
and give it a range name, starting with a backslash, say \Q, then press CtrlQ, in cell B2 appears: /C~{D}~.

We have a 7 character long quine.  Now if we add a {D} on the end, the code now copies itself down a row, then moves down to the next line, where it reads and carries out any code it finds there, resulting in a second copy, and a second move down, and so on, until the end of the universe, which in the case of this no longer brief computer code, is row 65536 of the spreadsheet:


All this talk of Quine and quining reminded me of Douglas Hofstadter, and a search on his name led me to xkcd 917:


Which completes the circular tour with a link back to this blog, wherein a work of Douglas Hofstadter is reduced to not 6 words, but a single two letter word:


Hope you enjoyed the trip.

Posted in Computing - general, Drawing, Newton | Tagged , , , , , | Leave a comment

Extracting numbers with regular expressions

Shortly after I wrote about extracting numbers from text strings, Winston Snyder at wrote a detailed article about using “regular expressions” to separate text from numbers in any string.  I have adapted his routine for the same purpose as in the previous post, that is to extract a single numerical value from a text string.  The regular expressions approach has two main advantages:

  • The same function can be used to extract numbers from the left, right, or middle of a text string.
  • No delineators are required.

The only drawback is that if the text string contains more than one number the function will concatenate them if they are integers, or return zero if they both have decimals.

The new ExtractNum function has been added to GetNum.xlsb, and  Text-in2.xlsb, and is shown in use in the screenshot below:

ExtractNum Function

ExtractNum Function

This function is only scratching the surface of what can be done with regular expressions. For more details and links see

Posted in Excel, UDFs, VBA | Tagged , , , , | 2 Comments

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:

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.

Posted in Excel, Link to Python, NumPy and SciPy, UDFs, VBA | Tagged , , , , , | Leave a comment