Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding keywords in several text columns
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding keywords in several text columns
Try this UDF:
Function findsubstr(substr) findsubstr = True On Error GoTo notfound hitrow = Worksheets("Munka2").Columns("F:G").Find(What:=sub str, 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: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding keywords in several text columns
On Mon, 3 Dec 2007 22:07:01 -0800, CFR wrote:
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding keywords in several text columns
Much better!
Stefi €˛Ron Rosenfeld€¯ ezt Ć*rta: On Mon, 3 Dec 2007 22:07:01 -0800, CFR wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding keywords in several text columns
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 "Ron Rosenfeld" wrote: On Mon, 3 Dec 2007 22:07:01 -0800, CFR wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding keywords in several text columns
Wow, I think what I just posted works, just without the ...???
Thank you so much! Have a great day. "Ron Rosenfeld" wrote: On Mon, 3 Dec 2007 22:07:01 -0800, CFR wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding keywords in several text columns
On Tue, 4 Dec 2007 10:05:02 -0800, CFR wrote:
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding keywords in several text columns
On Tue, 4 Dec 2007 06:36:01 -0800, Stefi
wrote: Much better! Stefi Thanks. Sometimes I, too, revert to VBA and overlook a simpler formula solution. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I search for keywords in cells containing text? | Excel Worksheet Functions | |||
Need Help!! Want to search through 3 columns for a list of keywords | Excel Worksheet Functions | |||
FINDING DUPLICATE TEXT IN 2 COLUMNS!!!!! | Excel Discussion (Misc queries) | |||
Keyword search, several keywords | Excel Discussion (Misc queries) | |||
Finding Duplicated Text within Columns | Excel Discussion (Misc queries) |