Marking Duplicates

D

Daniell

I have a large spreadsheet(7000 entries) that I would like to mark any of the
row that are duplicates. In order to match the duplicates columns A B C have
to be the same. I have attempted to use the following but it does not seem
to work:

=IF(COUNTIF(A:C,A2)>1,"Duplicate","")

I place this in column D then copy it down through the spreadsheet.

Thanks for your help.
 
R

Ron Rosenfeld

I have a large spreadsheet(7000 entries) that I would like to mark any of the
row that are duplicates. In order to match the duplicates columns A B C have
to be the same. I have attempted to use the following but it does not seem
to work:

=IF(COUNTIF(A:C,A2)>1,"Duplicate","")

I place this in column D then copy it down through the spreadsheet.

Thanks for your help.
Click to expand...

Hopefully someone has a faster method, but this formula:

=SUM(--((A1&" "&B1&" "&C1)=($A$1:$A$7000&" "&$B$1:$B$7000&" "&$C$1:$C$7000)))

array-entered (That means hold down <ctrl><shift> while hitting <enter>) and
copied down to row 7000 will return a count of the number of times A, B, and C
are found in the table.

It runs fairly slowly, though.




--ron
 
Top