0

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?

Improve this question

3 Answers 3

Reset to default
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.

IMG:

Improve this answer
1
  • Upvote for a neat solution, but I also posted an answer which does not require the latest version.
    – AdamV
    Commented Jun 11, 2021 at 11:34
1

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).

Improve this answer
4
  • very close to my solution: =IF(SUMPRODUCT(COUNTIF(B2:B8,B2:B8))>COUNTA(B2:B8),TRUE,FALSE) ... I think you should mention instructions to enter array formula with CSE
    – Alex M
    Commented Jun 11, 2021 at 15:36
  • Thanks..It worked perfectly. By the way, I AM using an old Excel version (2007). that's why I immediately went to COUNTIF.
    – lakawak
    Commented Jun 12, 2021 at 8:45
  • @AlexM I did not include instructions to "array enter" my formula use CSE since there is no need to in this instance. Also, you don't need to wrap an IF round a statement of inequality - it will naturally return True or False.
    – AdamV
    Commented Jun 15, 2021 at 11:53
  • @AdamV Is it not? I didn't think of trying without it; mine worked with it so I figured it was right. Interesting. Thanks!
    – Alex M
    Commented Jun 16, 2021 at 17:30
0

Did you mean you want to find the duplicate cell in range with conditional formatting?

For example: Select the Range A1:A8 and go to Home- Conditional Formatting- New Rule...- Use a Formula to determine which cells to format- set the Format.. and enter the Formula:

=COUNTIF($A$1:$A$8,A1)<>1

enter image description here

Improve this answer
2
  • That I know how to do. I just wanted a way to then have a cell underneath the range that says if there are duplicates or not, so I can quickly and easily analyze several sets at once.
    – lakawak
    Commented Jun 12, 2021 at 7:56
  • Basically, I'm trying to do an analysis of the fact that you only need 23 people to have a 50% chance that at least two will have the same birthday. So I am trying to expand on that. The easiest way to do the original is just have a 23 random numbers between 1 and 365. Then have a way to tell if there is a duplicate. From there, I want to analyze/"prove" by examples other ranges...like how long to get 50% chance of two random numbers being the same in a set of 1-1000. A simple TRUE/FALSE that I could could over 100s of columns would make that much easier than manually looking for red cells.
    – lakawak
    Commented Jun 12, 2021 at 7:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .