Very Cool Ways to Manipulate, Sort and Filter Lists and Tables – Dynamic Arrays

I have long worked with big datasets with multiple repeating values in field lists that I wanted to isolate into unique instances (names, emails, room types…). Previously, I used pivot tables to create summary lists that I would copy and paste to a new list. A few years ago, I came across this tip using “dynamic arrays” that is way simpler and saves me a ton of time!

Dynamic arrays sound complicated but they’re super simple. They are Excel functions introduced in Office 365. They can remove duplicates, sort, filter and lots more. The weird part is that you enter the array function in a cell like any other Excel function but the results “spill” over into adjacent cells. (The only thing to watch out for is that the adjacent cells are empty! ) The cool “dynamic” part is how these lists update automatically when the source data is changed or added to.

I use the UNIQUE and SORT array functions in updating the OCAPPA master membership list each month. This list is compiled from four separate sub lists with duplicate emails. I used to use pivot tables to isolate unique instances of these emails but the UNIQUE and SORT functions make it a snap!

Here is a link to a Youtube video by Leila Gharani, “5 Excel Functions EVERYONE Needs“, Jan. 7, 2021 [12:14]. The Youtube description below provides timestamps to the specific functions. But I do recommend checking out all five of her tips. As a bonus, she includes the VERY useful XLOOKUP function – a topic for another post! (Leila’s example works from an Excel table, but you can select your ‘arrays’ by dragging across a range of cells.) Check it out and let me know if you have any favorites.

• This topic was modified 1 month, 3 weeks ago by cmcarthur.