Highlighting duplicates using two columns in Excel

0
votes

I have a long list of cities and countries in Excel. It looks like this:

Aibak  |Afganistan
Andkhoy|Afganistan
...
Berat |Albania
Burren|Albania
Burren|Albania
...
San Francisco|Argentina
...
San Francisco|United States

I want to highlight cells that are copies. For example there are two "Burren"s that should be highlighted (both or only the other for deletion). However there might be multiple cities that have same name, but are in different countries, so they are not duplicates.

Is there a simple and fast formula for this? I have 20000 entries so some complicated formulas take too long time.

2
excelexcel-formuladuplicateshighlightconditional-formatting
Use simply IF() with CONCATENATE() formula or operator &. – Harun24HR
The simple and fast way would be adding a third column where you concatenate both cells (something like =A1&B1 should work). Then, in that third column, use a standar conditional formatting rule to highlight duplicates – Foxfire And Burns And Burns

2 Answers

2
votes

Select your range, apply new conditional format rule (formula):

=SUMPRODUCT(($A$1:$A$7=$A1)*($B$1:$B$7=$B1))>1

=SOMPRODUCT(($A$1:$A$7=$A1)*($B$1:$B$7=$B1))>1

A possibly better aproach (less calculating on 20000 records) could be:

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

enter image description here

This way you can highlight the rows that should be deleted.

0
votes

You could use a HELPERcolumn to concatenate both Cities & Countries

  1. Formula to create HELPER column:

=A2 & "_" &B2

  1. Use conditional formatting on Helper column. Select C2:C10 (HELPER Column) - Home - Styles - Conditional Formatting - Highlight Cells Rules - Duplicate Values - Press OK

enter image description here

Results

enter image description here