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.



This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

2 Responses to More Sort Functions

  1. Pingback: Excel Roundup 20140428 | Contextures Blog

Leave a Reply

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

You are commenting using your 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