Solved Duplicates Across Discrete Columns in Excel

October 14, 2015 at 12:47:50
Specs: Macintosh
I have thousands of rows of text data in two columns: Column A is City Name and Column B is County Name. I need to highlight all sets of matching data (ie, highlight each row where the city name is also the county name). I have tried using =AND(A$2=B$2) as a conditional formatting rule and also have tried =COUNTIF(A$2:B$2, A$2)=2. The problem is that both of these functions highlight everything that is repeated anywhere in the two columns. I only want a row to be highlighted if it matches discretely within the row (ie: if A2=B2 or A1257=B1257. I don't want it to highlight if A2 happens to have the same value as B1257). Any ideas?

See More: Duplicates Across Discrete Columns in Excel

Report •

October 14, 2015 at 13:18:06
✔ Best Answer
Try this in your Conditional Formatting Rule:

 1) Select your Range of Cells, A2 - B1257
 2) On the ribbon click Conditional Formatting
 3) Click on New Rules, it’s near the bottom of the dialog box.
 4) Click Use Formula to determine which cells to format.

 5) Enter the formula: =$A2=$B2

 6) Click on the Format button
 7) Select the Fill Tab
 8) Select a pretty color
 9) Click OK
10) Click OK

See how that works for you.


Report •

October 14, 2015 at 14:12:46
That works! Thank you!
Do you know if there is subsequently a way of simultaneously selecting all rows that are colored? (I want to get rid of all data associated with values where the city and the county have the same name).

Report •

October 14, 2015 at 15:06:50
Look here:

Depending on how long your list is, it may take a few moments to work, so be patient.


message edited by mmcconaghy

Report •
Related Solutions

Ask Question