COUNTIF Duplicate Detection

1

1

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!

DonkeyKong

Posted 2012-05-18T05:15:00.317

Reputation: 35

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. – None – 2012-05-18T05:49:32.287

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 – 2012-05-18T12:12:35.073

(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 – 2012-05-18T04:56:27.480

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 – 2012-05-18T05:18:20.623

Try the following array formula (Ctrl+Shift+Enter): =IF(sum(--(A$2:A$10750=A2))>1,"1","") – None – 2012-05-18T06:01:43.317

Answers

2

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

chris neilsen

Posted 2012-05-18T05:15:00.317

Reputation: 4 005

Asked: 2012-05-18T05:15:00.317

Viewed: 2 315 times

Active: 2012-05-18T05:33:56.113