Results 1 to 6 of 6

20040609, 14:14 #1
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula to find duplicates (2000)
I got this formula from this forum. =IF(MAX(COUNTIF(INDIRECT("A2:A"&(MAX((A2:A9<>"")*R OW(A2:A9)))),INDIRECT("A2:A"&(MAX((A2:A9<>"")*ROW( A2:A9))))))>1,"Duplicates","No Duplicates")
I put some numbers in cells A1:A9 like 123455671 just to test the formula
I copied the formula (from Word where I pasted it) into the formula bar and did controlshiftenter because it is an array
I copied it down and get all duplicates and obviously 2 3 4 6 and 7 do not have duplicates
One time it did work but I have been trying and trying and it won't work again. I would like to use this formula but I don't know why it worked once and now it doesn't stumped!!
Am I doing something wrong?

20040609, 14:31 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula to find duplicates (2000)
The formula is not one to be copied down the rows. It is a single formula to look for any duplicates (at all) in the list. 5 is a dup so the formula says "Duplicates". if you change the 5 to an eight it will say "No duplicates".
It does not check individual ones. Here is one that does. Add it to B1:
<pre>=IF(COUNTIF(A:A,A1)>1,"Duplicates","No Duplicates")</pre>
This can be copied down to give whether and individual item is duplicated elsewhere in the list. It is a normal function not an array.
Steve

20040609, 14:49 #3
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula to find duplicates (2000)
Thanks  that works fine but I did copy that other formula down and it did work. It said "no duplicates" at the first occurance and then indicated duplicate at subsequent occurances of the number.

20040609, 14:57 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula to find duplicates (2000)
That doesn't sound like the first formula worked, it produced the same answer for everything after the first row. That is the problem with the first formula, it can't be copied down, it has to be rewritten for each row it is used on.
Legare Coleman

20040609, 15:19 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula to find duplicates (2000)
What do you want the formula to do? I assumed that you wanted to look at each value and determine if there was more than 1. (hence my formula).
The one you listed, does not do this. It is designed to compare each value in the range to determine if the the range has ANY duplicates. If that is what you want,There is no need to copy it down the rows (it will not work correctly copying it down the rows, anyway: it is not "designed" to be copied.
If it did work on occasion, it was because the dataset was structured odd, not that the formula worked. It will most likely not give the correct answer if copied (though on occasion it will)
Steve

20040609, 15:25 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula to find duplicates (2000)
Another thought, If you want to only indicate, true DUPES (the first is unique, the rest are dupes, try this:
<pre>=IF(COUNTIF(A$1:A1,A1)>1, "Duplicate","Unique")</pre>
This can be copied down the rows or into other columns.
Steve