check text in cell for at least one keyword from a list on anothersheet
 

I have a list of company names, some of which were dummy companies set
up for testing.
those names are often things like 'dummy company' or 'test no.2
company' etc.

I'm currently doing a long manual workaround which involves doing
multiple iterations of custom autofilter e.g. contains 'test' delete
rows, contains 'demo' delete rows.

I'm trying to create a formula that output of which will be a single
value which I can autofilter against and then delete the offending
rows all in one hit.

i.e. check the text in this cell for the presence of any of the
keywords in this list and if you find one return the value 'found
one'.

I've tried using an array formula e.g.

{=FIND(Keywords!B2:B7,A2)}

and my list looking like
"test"
"demo"
"dummy"

but just get the error #VALUE?

help gratefully received.

check text in cell for at least one keyword from a list on another sheet
 

This formula returns TRUE for items containing a keyword:

=COUNT(INDEX(SEARCH(Keywords!$B$2:$B$7,A2),0))0


Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Brotherharry" wrote in message
...
I have a list of company names, some of which were dummy companies set
up for testing.
those names are often things like 'dummy company' or 'test no.2
company' etc.

I'm currently doing a long manual workaround which involves doing
multiple iterations of custom autofilter e.g. contains 'test' delete
rows, contains 'demo' delete rows.

I'm trying to create a formula that output of which will be a single
value which I can autofilter against and then delete the offending
rows all in one hit.

i.e. check the text in this cell for the presence of any of the
keywords in this list and if you find one return the value 'found
one'.

I've tried using an array formula e.g.

{=FIND(Keywords!B2:B7,A2)}

and my list looking like
"test"
"demo"
"dummy"

but just get the error #VALUE?

help gratefully received.

check text in cell for at least one keyword from a list on another
 

I've used this with a great deal of success:
=IF(NOT(ISERROR(MATCH(A1:A6,B1:B6,0))),A1:A6,"")

It is an array function, so you must enter it using Ctrl + Shift + Enter,
not just Enter.

Changes the ranges to suit your needs...


Regards,
Ryan--

--
RyGuy


"Brotherharry" wrote:

I have a list of company names, some of which were dummy companies set
up for testing.
those names are often things like 'dummy company' or 'test no.2
company' etc.

I'm currently doing a long manual workaround which involves doing
multiple iterations of custom autofilter e.g. contains 'test' delete
rows, contains 'demo' delete rows.

I'm trying to create a formula that output of which will be a single
value which I can autofilter against and then delete the offending
rows all in one hit.

i.e. check the text in this cell for the presence of any of the
keywords in this list and if you find one return the value 'found
one'.

I've tried using an array formula e.g.

{=FIND(Keywords!B2:B7,A2)}

and my list looking like
"test"
"demo"
"dummy"

but just get the error #VALUE?

help gratefully received.

check text in cell for at least one keyword from a list onanother sheet
 

Nope, just get a column of FALSEs...




On 10 Dec, 14:57, "Ron Coderre"
wrote:
This formula returns TRUE for items containing a keyword:

=COUNT(INDEX(SEARCH(Keywords!$B$2:$B$7,A2),0))0

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Brotherharry" wrote in message

...

I have a list of company names, some of which were dummy companies set
up for testing.
those names are often things like 'dummy company' or 'test no.2
company' etc.

I'm currently doing a long manual workaround which involves doing
multiple iterations of custom autofilter e.g. contains 'test' delete
rows, contains 'demo' delete rows.

I'm trying to create a formula that output of which will be a single
value which I can autofilter against and then delete the offending
rows all in one hit.

i.e. check the text in this cell for the presence of any of the
keywords in this list and if you find one return the value 'found
one'.

I've tried using an array formula e.g.

{=FIND(Keywords!B2:B7,A2)}

and my list looking like
"test"
"demo"
"dummy"

but just get the error #VALUE?

help gratefully received.

check text in cell for at least one keyword from a list onanother
 

also get just a column of blanks....?
chances of two independent approaches both producing the same lack of
effect are remote, so wonder if it's my settings somewhere?
using Excel 2007


On 10 Dec, 15:03, ryguy7272
wrote:
I've used this with a great deal of success:
=IF(NOT(ISERROR(MATCH(A1:A6,B1:B6,0))),A1:A6,"")

It is an array function, so you must enter it using Ctrl + Shift + Enter,
not just Enter.

Changes the ranges to suit your needs...

Regards,
Ryan--

--
RyGuy

"Brotherharry" wrote:
I have a list of company names, some of which were dummy companies set
up for testing.
those names are often things like 'dummy company' or 'test no.2
company' etc.

I'm currently doing a long manual workaround which involves doing
multiple iterations of custom autofilter e.g. contains 'test' delete
rows, contains 'demo' delete rows.

I'm trying to create a formula that output of which will be a single
value which I can autofilter against and then delete the offending
rows all in one hit.

i.e. check the text in this cell for the presence of any of the
keywords in this list and if you find one return the value 'found
one'.

I've tried using an array formula e.g.

{=FIND(Keywords!B2:B7,A2)}

and my list looking like
"test"
"demo"
"dummy"

but just get the error #VALUE?

help gratefully received.