Conditional format checking duplicate values across multiple columns

Milos

Board Regular
Joined
Aug 28, 2016
Messages
103
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:

CurrentlyWhat I need
ABAB
1880000657414118800006574141
1880000657414118800006574141
1880000688000018800006880000
2678959688000026789596880000
2678959688000026789596880000
2678959789456326789597894563
2678959897654426789598976544
2678959987456126789599874561

<tbody>
</tbody>
















Thanks in advance

Milos
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,996
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

Board Regular
Joined
Aug 28, 2016
Messages
103
Re: Conditonal format checking duplicate values across multiple columns

Thanks Eric!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,996
Re: Conditonal format checking duplicate values across multiple columns

Glad to help! :cool:
 

Forum statistics

Threads
1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top