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

Hi,
Check CPearson web

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

"Rod" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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

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

"Eduardo" wrote:

Hi,
Check CPearson web

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

"Rod" wrote:

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

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" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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

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.

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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

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

"Ms-Exl-Learner" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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" wrote:

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Rod" wrote:

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

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

"מיכ�ל (מיקי) �בידן" wrote:

I did n't check all the combinations but it seems as this one should work:
{=IF(ISNA(INDEX(A1:A6&B1:B6,MODE(MATCH(A1:A6&B1:B6 ,A1:A6&B1:B6)))),"No
Duplicates","Duplicates")}
*** This is an array formula, and is to be entered with CTRL+SHIFT+ENTER
rather than with simply ENTER.
The curly brackets {} are not to be typed manually, those are entered by the
€œExcel€�, when the formula is entered as an Array formula. ***
Micky


"Rod" wrote:

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

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

"מיכ�ל (מיקי) �בידן" wrote:

I did n't check all the combinations but it seems as this one should work:
{=IF(ISNA(INDEX(A1:A6&B1:B6,MODE(MATCH(A1:A6&B1:B6 ,A1:A6&B1:B6)))),"No
Duplicates","Duplicates")}
*** This is an array formula, and is to be entered with CTRL+SHIFT+ENTER
rather than with simply ENTER.
The curly brackets {} are not to be typed manually, those are entered by the
€œExcel€�, when the formula is entered as an Array formula. ***
Micky


"Rod" wrote:

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

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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


.