Duplicate check

[Ahmed Fawad]

[TABLE="width: 130"]
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 65"]A[/TD]
[TD="class: xl67, width: 65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]32[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]32[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]32[/TD]
[TD="class: xl65"]8[/TD]
[/TR]
[TR]
[TD="class: xl65"]33[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]12[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]9[/TD]
[/TR]
[TR]
[TD="class: xl65"]35[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]35[/TD]
[TD="class: xl65"]13[/TD]
[/TR]
[TR]
[TD="class: xl65"]35[/TD]
[TD="class: xl65"]16[/TD]
[/TR]
[TR]
[TD="class: xl65"]37[/TD]
[TD="class: xl65"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]37[/TD]
[TD="class: xl65"]11[/TD]
[/TR]
[TR]
[TD="class: xl65"]37[/TD]
[TD="class: xl65"]13[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

A and B has different values, IF Both values in A & B in a same is repeated again in other row there should be a conditional formatting by formula which indicates that's its duplicate values entered.


How to do it?

 

[FDibbins]

Based on your sample, which cells/values would get highlighted?

 

[Ahmed Fawad]

the latest one entered...

 

[FDibbins]

please provide a few samples, I cannot see which values/rows match

 

[Ahmed Fawad]

You can change in that same values in any row..... All I need to have a check both values in same row is not repeating in the list.... if so there should be an auto format or we can add another column where it shows that values in this row is duplicate.


Thanx

 

[vds1]

Select Column A & B > Go to Data > Remove duplicates > If your data has headers select My Data has headers > select columns A & B > Click ok

 

[My Aswer Is This]

In your sample above which one's would be highlighted?

 

[Ahmed Fawad]

[TABLE="width: 163"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Chk[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]32[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]32[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]32[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]33[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]35[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]32[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]33[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


See in above sample, Last two rows has the duplicate values from the list. We can set the Duplicate chk in 1st Column.


Thanx

 

[vds1]

Try this formula in C2

=IFERROR(IF(SUMPRODUCT(--((A2&"@"&B2)=$A$1:A1 & "@" & $B$1:B1)),"Duplicate","Unique"),"Unique")

Drag down

 

[vds1]

Array formula, You must CTRL+SHIFT+ENTER the formula, not just ENTER,

In C2

=IF(OR($A$1:A1&"@"&$B$1:B1=A2&"@"&B2),"DUPLICATE","UNIQUE")