Conditional format checking duplicate values across multiple columns

[Milos]

Hi,

Can somebody please lend their knowledge to help me fix a small issue. I have two columns of GPS coordinates values. I need a conditional format to check a couple of thousand numbers to know whether their are any duplicate coordinates. For the values to be duplicates they have to be duplicated across both columns in the same two rows (i.e. the numbers can be the same across one column but not across both columns).

I wanted to avoid doing duplicate values for each individual column as there are a lot of values that are duplicate in one column but not duplicate in both columns.

So far all I came up with was this non working conditional format rule: =AND(A1=A:A,B1=B:B) but I am not smart enough to fix the problem!

Here is an example of what I need in a couple of tables:

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Currently[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]What I need[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1880000[/TD]
[TD]6574141[/TD]
[TD][/TD]
[TD]1880000[/TD]
[TD]6574141[/TD]
[/TR]
[TR]
[TD]1880000[/TD]
[TD]6574141[/TD]
[TD][/TD]
[TD]1880000[/TD]
[TD]6574141[/TD]
[/TR]
[TR]
[TD]1880000[/TD]
[TD]6880000[/TD]
[TD][/TD]
[TD]1880000[/TD]
[TD]6880000[/TD]
[/TR]
[TR]
[TD]2678959[/TD]
[TD]6880000[/TD]
[TD][/TD]
[TD]2678959[/TD]
[TD]6880000[/TD]
[/TR]
[TR]
[TD]2678959[/TD]
[TD]6880000[/TD]
[TD][/TD]
[TD]2678959[/TD]
[TD]6880000[/TD]
[/TR]
[TR]
[TD]2678959[/TD]
[TD]7894563[/TD]
[TD][/TD]
[TD]2678959[/TD]
[TD]7894563[/TD]
[/TR]
[TR]
[TD]2678959[/TD]
[TD]8976544[/TD]
[TD][/TD]
[TD]2678959[/TD]
[TD]8976544[/TD]
[/TR]
[TR]
[TD]2678959[/TD]
[TD]9874561[/TD]
[TD][/TD]
[TD]2678959[/TD]
[TD]9874561[/TD]
[/TR]
</tbody>[/TABLE]
















Thanks in advance

Milos

 

[Eric W]

Re: Conditonal format checking duplicate values across multiple columns

Select columns A:B. Click Conditional Formatting > New Rule > Use a formula > and enter:

=COUNTIFS($A:$A,$A1,$B:$B,$B1)>1

click Format... and choose a red font color.

 

[Milos]

Re: Conditonal format checking duplicate values across multiple columns

Thanks Eric!

 

[Eric W]

Re: Conditonal format checking duplicate values across multiple columns

Glad to help!