Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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!

share|improve this question
(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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.