• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search Common text by comparing two text strings

A

amit_gupta123

Member
Hi

I am trying to compare two lists wherein the common words will get displayed in third column
Example
Col A Col B ColC ColD
Abc medical agency xyz drugs & medical INC Medical
Ab books store WA Book Store Book Store

I have list of 3000 records

If somebody can help with formula. It will make life better for me
 
Hi Amit Gupta ,

If you can upload your workbook with more than the two entries that you have posted , it will save a lot of time that will otherwise be spent in back and forth dialogue.

Narayan
 
Hi Amit ,

Is there any reason why you don't want VBA ? A VBA based solution is straightforward ; a formula-based solution is a tough one , and I am not sure that one is possible without using helper columns.

Narayan
 
Hi Amit ,

Check the file for a VBA based solution.

The macro is called Compare_two_ranges ; I have also created two named ranges Party_A and Party_B for the data in columns A and B. If you want to run the same macro in your data file , you have to :

1. Copy paste the macro to your file
2. Create the two named ranges :

Party_A referring to =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

Party_B referring to =Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B))

Narayan
 

Attachments

Hi Amit ,

If you start with a pre-defined list of keywords , and you want them to be matched against text strings in columns A and B , that is a simpler task which can be done using formulae.

Your initial requirement was to match two text strings of more than one word each , against each other ; this is more difficult to get using only formulae ; that is why I had suggested VBA.

Narayan
 
This is almost what I need but I need my result to come from a 3rd column.
I need to know what County each City/Town resides in.
How do I search for the each City (COL D) in the Municipality list (COL A) and give the County (COL B) result in (COL E)? =INDEX($D$2:$D$494,MATCH(1,COUNTIF(A12,"*"&$D$2:$D$494&"*"),0)) pulls the CITY name out of Municipality but I need to know the County.


Thx Tom
 

Attachments

You must log in or register to reply here.
Back
Top