New sort functions and sorting to groups

I first posted a user defined function (UDF) to sort spreadsheet or VBA data back in 2009.  Finally Microsoft have caught up, and Excel 365 subscribers now have two built in sort functions available:

=SORT(array,[sort_index],[sort_order],[by_col])
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

As an example of using the new functions I have looked at options or sorting a list into groups from Eng_Tips:

I have a long list of unique data in excel in the following form. As a first step I removed all the duplicates.
The unique headings always start with a “$_”. There are many more unique headings in the list other than GRIDS and MATERIALS.
I would like to filter the Original List, such that each data entry under a unique heading is merged under a single heading.

The examples shown below, including open source VBA code, can be downloaded from:
GroupSort.xlsb

The screenshots below show two options for performing this sort, using the Sort and Sortby functions:

The data to be sorted consists of group names, preceded by a $, followed members of the group.  To sort the data into continuous lists for each group requires a “helper column” showing the current group name for each row (see cell B4).  The data can then be sorted first by Column B, then Column A.  The sort function returns both columns, and every instance of the group name (and also every instance of group members, if these have been duplicated).

Tidier results can be returned using Sortby:

The Sortby function returns only the data to be sorted (specified in the first argument), and allows any number of “sort by columns”.  In this case Column B then Column A are specified.  The Unique function has been added to remove all duplicate elements.

For those who don’t yet have the sort functions, and to avoid the need for a helper column, I have written a GroupSort UDF, as shown below:

By default, GroupSort returns a 2D array, with one column for each group.  Setting the optional “ReturnArray” argument to False returns a single column, with duplicate group names removed.  Full open source code is included in the download file.

Another example using the Sortby function is shown below, to sort a list of names by the second name.  In this case the formula FIND(” “,A48:A71)+1,6) returns an array of the second names, which can be used as the data to sort on, without needing a helper column:

 

This entry was posted in Excel, 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 )

Google photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.