To identify duplicate and duplicates has to highlight

[Mahesh C A]

Dear Team,

Could you please help me to write VBA to identify duplicates and same has to highlight.

Please find below mentioned table to identify following duplicates and entire row should be highlighted.

Currently, I am using "CONCATENATE"(Country & Number) to identify duplicate and conditional format to highlight entire row as per status column.

[TABLE="width: 257"]
<tbody>[TR]
[TD]Country
[/TD]
[TD] Secuirty
[/TD]
[TD] Number
[/TD]
[TD]Satus
[/TD]
[/TR]
[TR]
[TD]GB
[/TD]
[TD] A1
[/TD]
[TD="align: right"]456
[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]GB
[/TD]
[TD] A2
[/TD]
[TD="align: right"]456
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD]US
[/TD]
[TD] B1
[/TD]
[TD="align: right"]789
[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]US
[/TD]
[TD] B2
[/TD]
[TD="align: right"]789
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD]EU
[/TD]
[TD] C1
[/TD]
[TD="align: right"]321
[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]EU
[/TD]
[TD] C2
[/TD]
[TD="align: right"]321
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD]HK
[/TD]
[TD] D1
[/TD]
[TD="align: right"]951
[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]HK
[/TD]
[TD] D2
[/TD]
[TD="align: right"]951
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance,

Best Regards,
Mahesh

 

[Johnny C]

Assuming the columns are A,B,C,D... in the Status column use the formula
=COUNTIFS($A$2:$A2,$A2,$C$2:$C2,$C2)
and copy down. This counts the number of times both the Country and the number have appeared up to that row. If it's the first occurrence, the value will be 1.
So your conditional formatting should use the formula
=($D2>1)

sometimes this isbetter depending on your requirements, in this case it would falsely give the first row a 1 so you would have to exclude conditional formating for row 2
It counts the number of times both the Country and the number have appeared between row 2 and the row above (hence why it won't work here)
=COUNTIFS($A$2:$A1,$A2,$C$2:$C1,$C2)
If it's the first occurrence, the value will be 0.
So your conditional formatting should use the formula
=($D2>0)

HTH

 

[Mahesh C A]

Assuming the columns are A,B,C,D... in the Status column use the formula
=COUNTIFS($A$2:$A2,$A2,$C$2:$C2,$C2)
and copy down. This counts the number of times both the Country and the number have appeared up to that row. If it's the first occurrence, the value will be 1.
So your conditional formatting should use the formula
=($D2>1)

sometimes this isbetter depending on your requirements, in this case it would falsely give the first row a 1 so you would have to exclude conditional formating for row 2
It counts the number of times both the Country and the number have appeared between row 2 and the row above (hence why it won't work here)
=COUNTIFS($A$2:$A1,$A2,$C$2:$C1,$C2)
If it's the first occurrence, the value will be 0.
So your conditional formatting should use the formula
=($D2>0)

HTH

Thank you so much !