Finding keywords in several text columns

[CFR]

Excel 2000: Need to compare text in cells in sheets.

One spreadsheet has simplified company names as keywords in one column (e.g.
B1 = "xyz", B2 = "abc corp" etc.).
The other has a large company database, in which 2 columns contain company
names in non-standardized format (e.g. columns F and G contains long lists
like "xyz llp", "company xyz", "xyz.com", "abc corporation", "def unlimited"
etc.)

I need to check if the company names on my list are anywhere in the two
columns that have company names in the other spreadsheet. Ie. for each co
name - is the name in there or not?
Simply should return Yes in column C1 etc. if (text in simplified keyword
list B1) is contained in any (text in the 2 database company name columns);
No if nowhere in those 2 columns.

I have not found a MS formula that helps me get the text comparison right.
Your thoughts? Thanks.

 

[Stefi]

Try this UDF:

Function findsubstr(substr)
findsubstr = True
On Error GoTo notfound
hitrow = Worksheets("Munka2").Columns("F:G").Find(What:=substr,
After:=Worksheets("Munka2").Range("F1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Row 'Activate
Exit Function
notfound:
findsubstr = False
End Function


Replace Munka2 with the sheet name of company database. Add
workbooks("company database file name") if it's located in a separate
workbook!

Regards,
Stefi

„CFR†ezt írta:

 

[Ron Rosenfeld]

Excel 2000: Need to compare text in cells in sheets.

One spreadsheet has simplified company names as keywords in one column (e.g.
B1 = "xyz", B2 = "abc corp" etc.).
The other has a large company database, in which 2 columns contain company
names in non-standardized format (e.g. columns F and G contains long lists
like "xyz llp", "company xyz", "xyz.com", "abc corporation", "def unlimited"
etc.)

I need to check if the company names on my list are anywhere in the two
columns that have company names in the other spreadsheet. Ie. for each co
name - is the name in there or not?
Simply should return Yes in column C1 etc. if (text in simplified keyword
list B1) is contained in any (text in the 2 database company name columns);
No if nowhere in those 2 columns.

I have not found a MS formula that helps me get the text comparison right.
Your thoughts? Thanks.

Something like (not tested):

=IF((COUNTIF("*"&B1&"*",'Sheet2!'rng1)+COUNTIF("*"&B1&"*",'Sheet2!rng2)) =
0,"No","Yes")


--ron

 

[Stefi]

Much better!
Stefi


„Ron Rosenfeld†ezt írta:

 

[CFR]

Thanks, Ron and Stefi.

Ron's suggestion ALMOST works - except that COUNTIF expects arguments
COUNTIF(range,criteria).

Keeping it at one range only to simplify, that would make it
=IF(COUNTIF('Sheet2!'rng1,"*"&B1&"*"...???) = 0,"No","Yes")

Can you please help with the ...???
TIA!

CFR

 

[CFR]

Wow, I think what I just posted works, just without the ...???
Thank you so much!
Have a great day.

 

[Ron Rosenfeld]

Thanks, Ron and Stefi.

Ron's suggestion ALMOST works - except that COUNTIF expects arguments
COUNTIF(range,criteria).

Keeping it at one range only to simplify, that would make it
=IF(COUNTIF('Sheet2!'rng1,"*"&B1&"*"...???) = 0,"No","Yes")

Can you please help with the ...???
TIA!

CFR

Just reverse the arguments I posted. (I didn't test it and had the range and
criteria reversed).
--ron

 

[Ron Rosenfeld]

Much better!
Stefi

Thanks.

Sometimes I, too, revert to VBA and overlook a simpler formula solution.
--ron