Use a formula to return Y if there are duplicates in a column of numbers

**Echooo3** · 07-07-2015, 11:48 AM

Is there a formula I can use that would return "Y" if there are duplicate numbers in a column?

I have a column named "IDnumbers" that contains thousand of entries in the column. I just want a quick way to know if there are any duplicate IDnumbers in that column. A quick look that returns either Y or N.

Thank you.

**gmr4evr1** · 07-07-2015, 11:52 AM

How about using conditional formatting the change the duplicate cells to a certain color like red?

**Echooo3** · 07-07-2015, 11:54 AM

I don't want to use conditional formatting because I just want to know if there is or isn't a duplicate in the column without having to visibly look at the whole column of numbers. But thanks for that suggestion.

**AlKey** · 07-07-2015, 12:41 PM

Try this

=IF(COUNTIF($A$1:$A$9,A1)>1,"Y","")

Row\Col	A	B
1	10	Y
2	10	Y
3	654
4	5
5	12	Y
6	2	Y
7	12	Y
8	32
9	2	Y

Or this

=IF(COUNTIF($A$1:A1,A1)>1,"Y","")

Row\Col	A	B
1	10
2	10	Y
3	654
4	5
5	12
6	2
7	12	Y
8	32
9	2	Y

**sourabhg98** · 07-07-2015, 12:43 PM

You can use the following formula -
=IF(MAX(COUNTIF(A1:A100,A1:A100))>1,"Y","N")
This is an array formula ...to use this, just copy it from here....click on the cell you want to paste to....press f2...now paste it using CTrl+V and then press Ctrl+Shift+Enter instead of just enter...

Here A1:A100 is the range which is looked for.
You can change it....

Hope that helps!!

**sourabhg98** · 07-07-2015, 12:49 PM

Let me tell you, my formula just looks up at the whole array, and just tells if there is a duplicate or not. So it just needs one cell to be placed on where you need a result "Y" or "N"....
It does not shows which number is having duplicates...
I did that just because you told that you don't want to visibly lookup the whole array...so you can use my formula to determine if there are any duplicates and then use conditional formula or formula by AlKey to find which value is having duplicates...