• 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
 
Yea sure..you are right..attaching a file with sample of 3 records...comparing col a with col b and results are in col c &d
 

Attachments

  • name match sample.xlsx
    8.3 KB · Views: 7
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

  • name match sample.xlsm
    14.3 KB · Views: 22
wow...........it's amazing and working.....
I have tried something ..pls check once....i have used some key words in Col d which are being matched with col a & colb
 

Attachments

  • name match sample - Copy.xlsx
    9.8 KB · Views: 9
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

  • city-county-muni search.xlsx
    8.7 KB · Views: 12
You must log in or register to reply here.
Back
Top