List contains duplicates

Are you anxious to know if a range or list contains duplicates or not? Here is an adequate guideline for you to run specific formulas on your range and check it.

FORMULA

The generic formula to check if a range of cell contains the duplicate value or not is:

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

OR

=SUMPRODUCT(COUNTIF(A2:A20,A2:A20)-1)>0

OR

=SUMPRODUCT((COUNTIF(list,list)-1)*(list<>""))>0

Where the list has named a range of cell range A2:A20 as per above formula syntax

Explanation

This is quite a simple formula which you need to use in smooth effective steps to check if a range contains any duplicates. Let’s see in detail how this formula actually works.

First, the COUNTIF formula is applied which analyzes each value in the range for you. The array of range or list that we provide is taken up as input criteria by the COUNTIF and then it gives out the result in the form of an array of counts. The array appears in the form of ones and two’s but after the subtraction of 1, appears in the form of zeroes and ones. Then the application of SUMPRODUCT on this array signifies the duplicates in the form of TRUE or FALSE.

In order to remove any chances of errors, you can use this alternative for the sake of increasing the reliability of your tests:

=SUMPRODUCT((COUNTIF(list,list)-1)*(list<>""))>0

This converts into 0 all the blank spaces which might cause any errors in the result.

So, by using this simple formula one can easily check the list contains duplicates to perform the error-free calculation.

Example

In this example, we made use of the COUNTIF formula in combination with the SUMPRODUCT formula. The formula that we used in the below example is the following:

=SUMPRODUCT((COUNTIF(list,list)-1)*(list<>""))>0

Where the list has named a range of cell range B3:B11. Here is how you can check whether a range or list contains duplicates or not as per below image.

Figure 1. Checking the list contains duplicates by a simple formula

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar