# COUNTIF Duplicate Detection

I have created a formula (excel 2003) to output a "1" if duplicates are found in a column.

=IF(COUNTIF(A\$2:A\$10750,A2)>1,"1","")

For some reason this formula does not mark the last duplicate in the in the file for all groups of duplicates. So if "Steve" is mentioned 4 times in column A, it will only mark the top three entries. If "Mike" is mentioned 6 times, it will only mark the first 5.

Any help is greatly appreciated!

-
(1) This is really a question for Super User (2) You need to provide more detail, the formula as is works (perhaps you have excess spaces in some data fields?) – brettdj May 18 '12 at 4:56
Just signed up for Super User - thanks! I have spot checked and none have had extra spaces or characters. What additional information would be helpful? – DonkeyKong May 18 '12 at 5:18
It's doing that because that's what you told it to do. Granted, I wouldn't have the foggiest idea how to "fix" it (though I'd probably throw in a `+1` into the formula), but Excel is doing exactly what you told it to do. – user3463 May 18 '12 at 5:49
Try the following array formula (Ctrl+Shift+Enter): =IF(sum(--(A\$2:A\$10750=A2))>1,"1","") – CaBieberach May 18 '12 at 6:01
I have tested your formula in Excel 2003, and it works totally fine except it take some time to calculate before all the "1"s show up for a large set of data (10750 rows). – wilson May 18 '12 at 12:12

The formula does work provided the top of the `COUNTIF` range is absolute (ie is A\$2 on all rows)
If the top of the range changes on each row (ie for example is `=IF(COUNTIF(A\$3:A\$10750,A3)>1,"1","")` on row 3) then it will behave as you describe.
You could try `=IF(COUNTIF(A:A,A2)>1,"1","")` instead