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:

Currently			What I need
A	B		A	B
1880000	6574141		1880000	6574141
1880000	6574141		1880000	6574141
1880000	6880000		1880000	6880000
2678959	6880000		2678959	6880000
2678959	6880000		2678959	6880000
2678959	7894563		2678959	7894563
2678959	8976544		2678959	8976544
2678959	9874561		2678959	9874561

<tbody>
</tbody>

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!