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 SortFunc.zip, 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.
Pingback: Excel Roundup 20140428 | Contextures Blog
Reblogged this on Sutoprise Avenue, A SutoCom Source.
LikeLike