Microsoft Excel: More Dynamic Arrays functions
In addition to the new SORT function, you may also find the new FILTER and UNIQUE array functions useful. For example, the worksheet on the next page contains a detailed listing of invoices in columns A through H (note that columns C, F, G, and H are temporarily hidden). An array-based FILTER function in cell L2 repeats the invoice listing in columns L through S, but only for those invoices issued to companies located in Los Angeles. You can see my video demonstrating the FILTER and UNIQUE functions in action at youtu.be.
In the worksheet below, I’ve also used the array-based UNIQUE function in cell J10 to create a listing of the unique cities found in the invoice listing. Then, in cell J4, I used the Data tab’s Data Validation tool to create a drop-down list, so the user can select different cities to view. For example, if the user navigates to cell J4 and selects Chicago, IL from the drop-down list, then only those invoices issued to companies in Chicago, IL will be displayed in the filtered invoice listing in columns L through S. In this example, these two array functions have been combined to produce the same type of filtering capabilities provided by the Slicer technology associated with Excel PivotTables, without having to produce PivotTables. You can download this example workbook at CarltonCollins.com/Filter.xlsx, but keep in mind these beta features won’t work until the new tools are released. You might be able to see them in action now if you have the latest version of Excel 365 installed, as an Office Insider.
Other array-based functions include SORTBY and RANDARRAY. The SORTBY function works like the SORT function, except it sorts data according to data contained in another array. For example, you might want to sort a report involving the partners in your CPA firm according to partner seniority, rather than alphabetically. The RANDARRAY function returns an array of random numbers whereby you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.
If you wanted to sort by “Sales,” you would want to sort by the second column, resulting in the formula =SORT(A3:B17, 2).
About the author
J. Carlton Collins, CPA, (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
