Highlighting Duplicate Values in Excel - adding alerts with Dynamic Array Formulas

Identifying and preventing unnoticed duplicates in your data can be extremely important in building robust, error free reports.
Iván made a goof point in the comments re data validation:
To prevent new duplicates being added at the bottom of the table I would name my range of cells in my table as MyList and then use =MAX( COUNTIF( MyList, MyList) ) =1
Here's a link to the file I created
aasolutions.sh...
Follow me on LinkedIn
/ wynhopkins
Twitter
/ wynhopkins
Access Analytic Training
accessanalytic...

[@utubeAgape]

Wow, I will use this for so much of the spreadsheets I work with - which have thousands of rows. Thank you!!!

[@AccessAnalytic]

Glad it was helpful Irene

[@patrickschardt7724]

Great tutorial. Alt + Enter and spaces are great tricks for cleaner and easier to read formulas. I use one space between all arguments in a function and two between things like value if true and value if false Things that were very har for impossible are now possible and very easy thanks to the dynamic arrays

[@AccessAnalytic]

Thanks Patrick Appreciate you sharing extra tips here. Dynamic arrays are awesome!

[@Luciano_mp]

Wow, very good. Thanks!

[@AccessAnalytic]

Thank you Luciano

[@ramanhai6205]

Thank you so much sir ...this is exactly I am looking for.

[@AccessAnalytic]

Glad to help Raman

[@ramanhai6205]

@@AccessAnalytic really I was in need of entering budget codes for the projection which should be unique And remains unchanged after deleting any row in between. I can go with this method and it makes sense for me. Thanku again. I am a regular follower of your videos. ❤️❤️ From india

[@IvanCortinas_ES]

Excellent. In last scenario I think that in this case the formula would be better, to control future records in our table: =MAX(COUNT.IF(B:B,B:B))=1 Otherwise, data validation would allow you to record repeating data when entering new data.

[@AccessAnalytic]

Good point Iván. I’d make my list a named range and then have =MAX( COUNTIF( MyList, MyList) ) =1 I try to avoid entire column references wherever possible

[@hampagna5090]

😍😍😍

[@sachin.tandon]

Nice work. This might be helpful. I used to get very odd behaviour using COUNTIF embedded in a lambda function that was called from within another LAMBDA (something to do with it not accepting the variable which although was a vector, was strictly not a RANGE, which it must take as its first argument). It did my head in! This formula avoids using COUNTIF, and IMO is more robust (when doing complex and nested lambdas) . Hope its useful COLUMN_DUPLICATES = LAMBDA(col_vector, LET( count, SCAN(0, col_vector, LAMBDA(a, r, SUM(--(r = col_vector)))), duplicates, IF( IFERROR(ROWS(UNIQUE(FILTER(col_vector, count > 1))), 0) = 0, "No Duplicates", UNIQUE(FILTER(col_vector, count > 1)) ), duplicates ) );

[@PurplePasion]

Thank you!!! You just made my life so much easier.

[@AccessAnalytic]

You're welcome Mariana

[@cuteness_]

This is awesome, thanks for sharing this video! I just want to ask if there's a way to highlight the duplicates in alternative colors (just two colors) so that I won't do it manually after I use the countif formula?

[@AccessAnalytic]

As far as I'm aware the Conditional Formatting - Highlight Duplicate Values option only allows you to chose one colour at a time.

[@cuteness_]

Yeah, that’s what I'm thinking too. 😢 btw, i like your voice! Very soothing.. ❤

[@adamstaples3984]

Great Tutorial! saved me a ton of work! Thanks!

[@AccessAnalytic]

Thanks for letting me know this was useful Adam. Greatly appreciated 😀

[@malejandrahorvath]

Wow! This is so helpful. Thank you!!!

[@AccessAnalytic]

You’re welcome Alejandra

[@johnwatson8203]

Really appreciate this tip! Thanks

[@AccessAnalytic]

You’re welcome John

[@healthteaspoonie]

This is a phenomenal video! Thank you! Can you show me how to make a spread sheet with multi data lines and duplication. For example: last name, first name and date of birth. I need to make it to where the duplicate data is flagged based on those 3 columns not just 1

[@AccessAnalytic]

I would add a column using TEXTJOIN to combine the 3 fields you need then check for duplicates in that one column

[@zamriyusof7511]

How to hightlight duplicate cell with different color? For example, duplicate A with red color, duplicate B in green color.

[@AccessAnalytic]

I think you’d need to add a helper column with a formula that flags if a value is a duplicate then lookup a colour from a lookup table ( A red, B, green etc) then add conditional formatting rules that look at that helper column.

[@kitty1977]

I love the Alt+Enter idea within the dynamic formulas. I have only used it on basic stuff at work but never considered it in the formulas. This is great. Thanks.

[@AccessAnalytic]

Great, thanks so much for letting me know you found it useful.

[@kitty1977]

@@AccessAnalytic you're welcome.

[@pipo441]

My unique function still shows all the values.... not unique... I use horizontally though.

[@AccessAnalytic]

Try =UNIQUE( range, TRUE )

[@florincopaci6821]

Thank you for this video! If it s possible maybe in the future you do a video using MMult in combination with other functions like transpose or match.Please.Thank you once again for this video

[@AccessAnalytic]

You’re welcome Florin. Thanks for letting me know you liked it 😀. I’ve never used MMULT so it’s unlikely I’ll do one on that sorry.

[@realpulsecoin]

Great Wyn, you can offcourse lock the cells for being edited? So no one can break the datavalidation? 🌞

[@AccessAnalytic]

Thanks Rasmus, yeah but If you want to allow people to enter data then locking doesn’t help protect the validation

[@wayneedmondson1065]

Awesome Wyn! Lots of creative ways to use dynamic array functions. Thanks for sharing :)) Thumbs up!!

[@AccessAnalytic]

Cheers Wayne, thanks for leaving a comment

[@GeertDelmulle]

this is a really good one! Will come in very handy for all of us. Did learn something new here: a nice data validation option - haven’t ventured so far down that list, and now I will. Thanks!

[@AccessAnalytic]

Cheers Geert

[@EricHartwigExcelConsulting]

Great video! I can not wait to start using this! Thank you for making/sharing this!

[@AccessAnalytic]

You’re welcome Eric

[@GrainneDuggan_Excel]

One more reason I want dynamic arrays at work! Lovely solution Wnn

[@AccessAnalytic]

They really are a dramatic change to Excel’s power

[@JonathanExcels]

Thanks for the tip on adding spaces. A tip in return…ctrl+shift+u toggles the expand/collapse formula bar.

[@AccessAnalytic]

Cheers Jonathan

[@GeertDelmulle]

Indeed. Use that myself, all the time. One step further still: got myself an Elgato Stream Deck, created an Excel profile and put that shortcut on a key there. Now I can fly even harder, and people I help via Teams meetings with their Excel problems can’t fathom how fast I can do (some) things... :-)

[@niciamejia227]

Is there a way to highlight and flag when a value appears three times (or any other specific amount of times) instead of twice? I need to be able to set a conditional format for a column that does not yet have any values in it. But when it does, it will have many and I want to be able to easily spot when it shows the same number three times. Can that be done on Excel?

[@AccessAnalytic]

Yep, highlight your range of values (e.g. A1:A10) go to Conditional formatting > New Rule > Use formula. =COUNTIFS($A$1:$A$10,A1)>=3 Should work

[@niciamejia227]

@@AccessAnalytic It worked!! Thank you! I will be using this a lot.

[@hrmnpsrffn]

Is there a way for rather than stopping, it only inform us that the data has been entered previously?

[@AccessAnalytic]

Yep, you can change the data validation to Warning rather than Stop. It's part of the data validation set up box

[@hrmnpsrffn]

@@AccessAnalytic Thank you so much for the knowledge, sir. 😁

[@AccessAnalytic]

No worries