In late September 2018, Microsoft revealed a number of fascinating new Excel worksheet functions.
https://techcommunity.microsoft.com/t5/Excel-Blog/Preview-of-Dynamic-Arrays-in-Excel/ba-p/252944
Bill Jelen has churned out an amazing 66-page ebook on the new functions.
https://www.mrexcel.com/download-center/books/2018/ExcelDynamicArraysStraightToThePoint.pdf
I strongly recommend that you look at the links listed above. Only then will you be able to appreciate what is presented in this article.
But, I don’t have immediate access to looking at these functions in the newest versions of Excel. So, that started me thinking: Could something similar to this work in a user-defined function (UDF)? My latest UDF creation, along with links to other interesting uses of UDFs can be viewed here.
Well, to make a long story short, it can and furthermore they operate on the ORIGINAL DATA. This means they can filter a dataset in place and sort data in place instead of creating a duplicate dataset or subset like the new Excel functions do. You may ask how a UDF, entered in a worksheet cell remote for the data, can filter and/sort that table of data, and even I did not think that it was possible, or I would have exploited this long ago.
So, the FILTERFUN function presented here (I would have called it FILTER but I would not want it to conflict with the new Excel functions if used alongside them) can filter a table of data. In the figure shown below, the FILTERFUN function is shown along with a set of data. This UDF has 2 arguments, the field in the table to be filtered and the criteria to be used for the filter.
If you are familiar with using Excel’s advanced data filter, you will note that the criterial in the 2nd argument uses the same syntax and has wildcard filtering abilities. The result for entering this formula can be seen in the next figure.
The code for the FILTERFUN function fixes the location for the table to start in cell A1, but that can easily be modified, as can be seen in the code for the next magical UDF, the SORTFUN function.
This UDF has 3 arguments: the table range, the field to be used as the sort key, and the desired sort order, as shown:
In this case, when the SORTFUN function is entered, the desired sort of the table is performed.
The following code for both of these functions is shown below.
These functions provide a utility that can be made to mostly emulate the new Excel FILTER and SORT functions, while also allowing the desired result without creating new data tables.
I hope you find this technique useful. If so, share it with your Excel friends and colleagues.
The Excel file can be downloaded here:
Excel For You 