Gandalf's notebook: Using Array Formulas in Excel - Find if a list has duplicate items

From http://chandoo.org/wp/

Using Array Formulas in Excel - Find if a list has duplicate items: "

Find a list is unique or not using array formulas in excel Today I have learned this very cool way to find if a list has duplicate items or not.

This technique uses array formulas (do not shudder, believe me they are not as difficult as you may think)

First the formula

Assuming your list is in the range, C3: C9, the array formula to find if a list has duplicate items or not is,

=IF(MAX(COUNTIF(C$3:C$9;C3:C9))>1,"list is not unique","list is unique")

Now the explanation

How do you know if a list has no duplicates? Simple, we find the number of times each item has appeared in the list and see if any of those counts are more than 1.

Now, take a look at the formula. It says find the maximum of individual item counts using countif (learn excel countif function) and if the maximum is more than 1, then the list has duplicates, otherwise it is unique.

But…

Yes, entering the formula will not work by itself. You have to make it array formula.

How do you do that?

Oh, that is simple, you just take the excel spreadsheet and whack it until it turns blue.

well, not really. all you need to do is enter the formula and press CTRL + SHIFT ENTER instead of just pressing enter.

that way excel converts your formula to array formula and the COUNTIF(C$3:C$9,C3:C9) will return an array of counts instead of one value. Now you can also guess why we have absolute reference for one parameter of countif () and relative reference for another. Learn more about Absolute and Relative References in excel formulas.