Excel Formula for showing repeated values


Excel Formula for showing repeated values




Let's say, all your values are in the range $A$2:$A$1000. In fact, this range is chosen larger than your actual data so that you don't need to adjust it whenever a couple of new rows are added. Your actual data might only have 500 rows, like that.

The following formula will count how many times the value in A2 is found in A2:A1000.


You can copy that formula down all the way to row 500 or 1000. That will put a lot of zeroes in the rows where column A is blank. Therefore you modify the above. The formula below will display a blank cell if there is no value in column A.


However, you aren't really interested in the count. You only want to show how many duplicates there are. For that you modify the formula further, like this.


But perhaps you don't want to know how many duplicates there are. Your flexible question allows for the possiblity that you merely what to know whether a particular value is unique in the list. The formula below would provide that information.

=IF($A2="","",IF(COUNTIF($A$2:$A$1000,$A2)>1,"There is at least one duplicate","This value is unique"))

You can modify the text between quotation marks to suit your preferences.


Answer the Question

You must create an account to use the forum. Create an Account or Login