Hi - Dave here.
How can you test if a range contains duplicate values?
This is one of those Excel problems that seems simple but is actually a bit tricky.
One nice approach is to use the COUNTIF function with the OR function:
=OR(COUNTIF(range,range)>1)
This elegant formula uses COUNTIF to generate an array of counts, and the OR function to evaluate results. The worksheet below shows the formula in use:
[
Download the workbook and read the full explanation]
Note: this is an array formula and must be entered with Control + Shift + Enter in older versions of Excel. See article for more information.
More Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
