Marking duplicate entries based on multiple columns

[Toney]

Hi all,

I have a list of files that I'd like to mark the duplicates. I can't use
just the file name because some files have the same name but aren't the same
file. As I understand it COUNTIF can't be used with multiple comparisons.
I've tried CONCATENATE several of the columns and then using COUNTIF but it
doesn't seem to work.

Example of what I'm looking for....

File A, 400k, 9/23/09, Duplicate
File A, 450k, 9/23/09
File A, 400k, 9/23/09, Duplicate

Thanks in advance
Toney

 

[Jacob Skaria]

Try the below(all in one line)

=IF(SUMPRODUCT(($A$1:$A$100=A1)*($B$1:$B$100=B1)*($C$1:$C$100=C1))>1,"Duplicate","")

If this post helps click Yes

 

[Toney]

DOH!

I didn't carry the function down the entire column. Once I did, using
CONCATENATE and COUNTIF worked.

Well if anyone else needs this info what I did was....
In D1 =CONCATENATE(A1,B1,C1) where A1 is name, B1 is size, C1 is date.
In E1 =IF(COUNTIF(D$1***,D1)>1,"Duplicated","") where *** is the last
entry in Column D

Thanks for looking and if anyone has a slicker way of doing it, please reply.

Toney

 

[Jacob Skaria]

With the SUMPRODUCT() formula applied in D1 and copied down...you dont need
to have a temporary column...

If this post helps click Yes

 

[Toney]

Jacob,

Thanks for the reply. Your method worked.

Toney

 

[Toney]

FYI to anyone looking at these answers.

If you're using large amounts of data like I am for this project (25,809
rows) both solutions take a very very long time. This is not something you
want to if you're in a hurry.

Toney