check for duplicates

[Rod]

Hi all,
I have the data as shown below
col A col B
1 a
2 a
3 a
1 a
1 b
2 b
Is there any formula to find duplicates( 1st and 4th rows are duplicates)
are there or not?
I am using the sumproduct formula in supporting column C
But I need the formula in C1 which displays "Duplicates" if there are or
else "No duplicates"
Thanks in advance

 

[Eduardo]

Hi,
Check CPearson web

http://www.cpearson.com/excel/Duplicates.aspx

 

[Ms-Exl-Learner]

Paste the below formula in C1 cell

=IF(OR(A1="",B1=""),"",IF(SUMPRODUCT(($A$1:$A1=$A1)*($B$1:$B1=$B1))>1,"DUPLICATES",IF(SUMPRODUCT(($A$1:$A1=$A1)*($B$1:$B1=$B1))=1,"NO DUPLICATES","")))

Copy the C1 cell formula and paste it for the remaining cells of C Column.

Remember to Click Yes, if this post helps!

 

[Rod]

Thanks for the reply, but i could not able to get from that.
I need to find duplicates comparing with 2 columns as said 1st and 4th rows
duplicated

Please help me
once again thanks in advance

 

[Rod]

Thanks, but I need a formula like this
=IF(MAX(COUNTIF(A2:A11,A2:A11))>1,"Duplicates","No Duplicates")
but it will check two columns A and B

Help me

Thanks in advance

 

[Ms-Exl-Learner]

May be this...

Paste the below formula in C1 cell

=IF(COUNTIF(A:A,A1)=1,"NO DUPLICATES",IF(COUNTIF(A:A,A1)>1,"DUPLICATES",""))

Copy the C1 cell formula and paste it for the remaining cells of C Column.

Remember to Click Yes, if this post helps!

 

[T. Valko]

Assuming no empty cells in either range.

Try this array formula** :

=IF(SUM(IF(FREQUENCY(MATCH(A2:A7&B2:B7,A2:A7&B2:B7,0),ROW(A2:A7)-ROW(A2)+1),1))=COUNTA(A2:A7),"No
Dupes","Dupes")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Rod]

Sorry, may be I could not able to explain breifly
Say in column B are the folders and in column A are the files
I need to find whether are there any duplicates files(cloumn A) in any of
the folder(column B)

Thanks in advance

 

[Rod]

Thank you for responce. You got my point
it is showing as duplicates even though i change as no duplicates( A4
changes as 4)

Help me

thanks in advance

 

[Rod]

awesome....micky..... many thanks
it is working but i put 0 in match function
=IF(ISNA(INDEX(A1:A6&B1:B6,MODE(MATCH(A1:A6&B1:B6,A1:A6&B1:B6,0)))),"No
Duplicates","Duplicates")
Actually I have large data of 60000 records
Could you please explain the logic behind it

Thanks in advance

 

[Rod]

Wonder.... many more thanks
please explain the logic behind it
thanks in advance