check for duplicates

  • Thread starter Thread starter Rod
  • Start date Start date
R

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
 
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!
 
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
 
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
 
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!
 
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.
 
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
 
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
 
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
 
Back
Top