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

Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.