List contains duplicates

An excel formula to list contains duplicates

Related Functions

Sponsored Link

=SUMPRODUCT(COUNTIF(data,data)-1)>0

If you want to test a list for duplicates, you can do so with a formula that uses COUNTIF together with SUMPRODUCT.

In the example, there is a list of names in the range B3:B11. If you want to test this list to see if there are duplicate names, you can use:

 
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0

How this formula works

Working from the inside out, COUNTIF first gets a count of every value in B3:B11 in the range B3:B11. Because we supplying a range (array) of cells for the criteria, COUNTIF returns an array of counts as a result. In the example shown this array looks like this:

{1;2;1;1;1;1;1;2;1}

Next 1 is subtracted, which yields an array like this:

{0;1;0;0;0;0;0;1;0}

Note that every 1 in the array (i.e. items that appear just once) has been converted to a zero.

Next, SUMPRODUCT adds of the elements in this array and returns the result, which in this case is the number 2, which is then tested for a >0 value.

Any time a list contains duplicates, there will be at least two 1's in the array summed by SUMPRODUCT, so a final result of TRUE means the list contains duplicates.

 

Sponsored Link

0 votes. 0 / 5