Is there a way to tell if a range of cells has any matching cell?

I am trying to find a way to get Excel to take a range of cells and find out if ANY of those cells match any other cells in that range. So far, all I can find is to determine if any in that range matches a specific cell or criteria given. But I want it to be able to just find out if there are any matches. So, a column that is, say, 109, 100, 57, 83, 2, 100, 86, 82 would result in TRUE, or maybe a 1 if that is how I could set it up. Either way, it would just let me know that the criteria has been met because there are two 100s in the range.

I'm thinking I cold do a long convoluted nested COUNTIFS formula, using one of each cell, but is there a simpler way to do this? If the range is 50 cells, I don't want to make 50 ranges and criteria in the COUNTIF formula. I know I could manually check it by doing a conditional formatting on the range, having it highlight cells that are matching, and then underneath the range manually put a 1 or whatever whenever there is a highlighted cell. But is there a way to make that conditional formatting do it for me?

Solution 1:

Assuming your range is from A3 to A10 and uses Office365, try the following formula:

=IF(COUNT(A3:A10)<>COUNT(UNIQUE(A3:A10)),TRUE)

It counts the number of items in the range and compares it to the count of unique items. If there are different (TRUE), it means there is at least one duplicate.

Solution 2:

Breaking this down, you need to somehow get a count of how many times each number appears in the list and see if any of those are >1. One way would be to use sumproduct to multiply each number by it's own count, then compare that to the overall sum. They will match only if all the counts are singles (=1). For example:

=SUMPRODUCT($A$1:$A$100,COUNTIF($A$1:$A$100,A1:A100))>SUM($A$1:$A$100)

Is only True if there is a duplicate, and False if all are unique. While this uses arrays inside the SUMPRODUCT, you don't need to "array enter" this and use curly braces etc.

You can do this with any version of Excel from this century (I'm pretty sure COUNTIF is over 20 years old now).