How to Match and Highlight two lists with similar data(names)?

[Radha]

Conditional Formatting works when the two lists have exact matches

List 1 List 2

ATT ATT

Verizon VERIZON

Sprint Sprint…….


Question:


But i want to match and highlight two lists which has similar names.


Example:


List 1 List 2

Airtel Airtel Pvt Ltd

BSNL BSNL Public Ltd

Tata Tata Sky Ltd

JP Morgan J.P. Morgan

Southwest United

Alaska Virgin


Action Required:


I want to compare List 2 with List 1 and highlight the names in List2 that have similar match in List 1


Expected Output:


The following names in List 2 are to be highlighted:


Airtel Pvt Ltd

BSNL Public Ltd

Tata Sky Ltd

J.P. Morgan

(Since they have similar match in List 1).

 

[SirJB7]

Hi, Radha!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s), maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

http://chandoo.org/wp/2011/10/27/compare-2-lists-visually-and-highlight-matches/


Regards!

 

[Radha]

Here's the link for the Spreadsheet (if needed):

https://www.dropbox.com/s/o3ko1ocssm4pupr/Match%20%26%20Highlight%20Similar%20Names.xlsx

 

[Radha]

@Sir JB7:

I checked all those links before posting this one but couldnt find what i was looking for..


Regarding Posting protocols, will correct it if any mistakes made now....

 

[SirJB7]

Hi, Radha!


Reading again your post, I think that it'd be hard enough to provide you with a solution if you first don't define how would you do the matching. No need to be explained by formula, maybe just a text that tells -specifically and unequivocally- the process you want to implement in Excel.


It'd be much more simple -even if still difficult- if the inexact matches would be done by the first characters, otherwise... we'll be waiting to read your instructions.


Regards!

 

[shrivallabha]

Hi Radha,


Apply following conditional formatting formula in cell D9:

=ISNUMBER(LOOKUP(99,SEARCH(TRIM($C$9:$C$16),D9,1)))


This formula will take care of most of the cases but won't be able to handle cases like JP Morgan to J.P. Morgan sort.


Maybe a function like fuzzy VLOOKUP will work. See following link:

http://www.mrexcel.com/forum/hall-fame-winners/69649-alans-udfs-fuzzy-match-problem.html