Microsoft Excel: More Dynamic Arrays functions

By J. Carlton Collins, CPA

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.

techqa-3


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.

SPONSORED REPORT

Tax reform complicates year-end tax planning

Get your clients ready for tax season with these year-end tax planning strategies, which address how to make the most of recent tax law changes, such as the new deduction for qualified business income and the cap on the deductibility of state and local taxes.

VIDEO

What RPA is and how it works

Robotic process automation is like an Excel macro that can work on multiple applications, says Danielle Supkis Cheek, CPA. RPA can complete routine, repetitive tasks such as data entry, freeing up employee time from lower-level chores.