I have a large list which in column A contains the a list of company names
and column B contains another list of company names Wht I would like to do
is compare these two columns and easily pick out the companies that contain
the same word within their names. Is there a function to do so?

Hi,

AFAIK, there is no easy way to check is any word of string 1 is in string 2.

You can check if a particular string is in string 2.

Otherwise you need some vba to search through the string.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Billing" wrote:

I have a large list which in column A contains the a list of company names
and column B contains another list of company names Wht I would like to do
is compare these two columns and easily pick out the companies that contain
the same word within their names. Is there a function to do so?

If it's okay to check the words one at a time, then I'd suggest a "Finder"
function which would "light up" all companies in columns A & B that contain
the word you type into a particular cell. If that solution meets your needs,
here's how to do it.

In an open cell such as C1, type the word "Finder". Below it in cell C2,
highlight the cell's background and set a border around it to distinguish it
as a cell you are going to type into. Then, in cell A1 where your first
companies list starts, go to Conditional Formatting (under the format menu)
and do the following:

Under Condition 1:
- set the drop down to "Formula Is"
- in the open space type the following: =$C$2="" (that's two double quote
marks)
- leave the Format alone ("No Format Set")

Under Condition 2:
- set the drop down to "Formula Is"
- in the open space type the following: =ISNUMBER(FIND($C$2,A1))
- Press the Format button and set up a format such as a background color or
bold, colored font (or both!)

Press Okay. Then, with cell A1 still selected, click on the Format Painter
and copy the conditional format you just created to all the other cells in
columns A and B.

Now when you type any word in C2, all companies containing that word will be
reformatted, and easy to see.

In case you're wondering, the first condition you set up prevents everything
from being reformatted when you clear out the contents of cell C2. Without
that first condition, when C2 is empty Excel would highlight every cell in
columns A & B with a space character.

Even slicker: If the list of all the words you will want to look for is
known, you can save time and make the Finder function more reliable with a
drop-down list instead of just typing the search words. Key in the list
somewhere (even another tab) with the words in separate cells in one column
and then name that list (highlight all the names then use Insert Name
Define). Back on your main spreadsheet, in cell C2 set up a data validation
drop-down using that list (Data Validation Allow: List Data:
"=yourlistname"). Now you have a drop-down list of the words, and just
select one at a time to find the matching companies.

Hope that helps. Write back if stuck.

"Billing" wrote:

I have a large list which in column A contains the a list of company names
and column B contains another list of company names Wht I would like to do
is compare these two columns and easily pick out the companies that contain
the same word within their names. Is there a function to do so?