highlight matches between 2 lists

[DrRJE]

Hi, I did a bit of searching but didn't find a solution quite yet...hoping someone can help me out.

I have a column A - 3,129 locations that I will accept (no duplicates within this location list).

I also have a column B - 17,318 locations from all over the world (there are many duplicates in this list).

If I could remove the duplicates from column B, I could do a conditional format, but I want to keep all the duplicates and highlight them all -- if they appear in column A.

So, can I highlight the cell in column B (or better yet, the row the cell is in) if that location is in my list (column A)?

Thanks so much for your help!

 

[crook_101]

Hi,


Welcome to the Board

Yes you can!!

Assuming your data is laid out as below:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Header 1</TD><TD>Header 2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">40</TD><TD style="TEXT-ALIGN: right">35</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">23</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">10</TD></TR></TBODY></TABLE>

Sheet1​

Highlight cell B2 and click on conditional formatting,>>new rule>>use a formula to determine which cells to format and enter the formula below:

=COUNTIF($A$2:$A$3129,B2)=1

then hit OK. Press control and C to copy this cell, highlight the rest of your range in column B aand then Paste Special - Formats only this will highlight all dupes in B when compared to column A

HTH
Ian

 

[DrRJE]

dude...thanks!

 

[DrRJE]

is there a simple solution like that to filter for more than 2 items at once?

I could set it up the same way, by putting all the words I want to filter for in one column, but the difference would be that it wouldn't have to be an exact match with that column...just contain that word.

sample usage is as a profanity filter in usernames.

column A is all the profanity words I will not allow

column B is all the usernames in my database.

if b*tch is in my list of unacceptable profanity, and "myb*itch" is a username, I'd want it to be highlighted.

I know I can do that with a simple filter, but I'd love to run it against my whole list instead of 2 at a time.

thanks!!