Dynamic Arrays: Identify Rows That Have Duplicates Using COUNTIFS and Excel’s New Calculation Engine

/in /by

This video is based on a solution that Alan at Computergaga solved by using Data Validation (see his video: https://youtu.be/1GDgTN4lvAo)

Of course, I started thinking: Dynamic Arrays. The challenge:
How can I flag a row that has duplicate entries?

My solution doesn’t use any of the new functions (e.g., FILTER, RANDARRAY, SEQUENCE, etc.). Instead, it takes advantage of the new calculation engine and uses COUNTIFS and MAX. The solution was much easier than I expected.

After we solve the problem in this video, we can have a little fun! How about using the Wingdings font to create the flag for offensive rows?

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2