Like match between 2 ranges
 

Hi,
I'm trying to flag records that CONTAIN any of the strings in another
range. My purpose, in this specific case, is to examine a range of
website URLs and to remove any URLs belonging to particular domains.
For example:

ColumnA: List of URLS
www.sports.cnn.com/basketball.htm
www.aaa.baddomain.com/trouble.htm
www.groups.google.com/excel/
www.nothanksdomain.com
http://www.trouble.dontwantdomain.or...ouble/go/away/

ColumnB: List of domains to suppress
baddomain.com
nogooddomain.net
dontwantdomain.org
nothanksdomain.com

ColumnC: Result
I'm looking for a formulae that identifies if there is a LIKE match
between ColumnA and ColumnB and outputs TRUE/FALSE (or similar).

Thanks
Dean

Hi!

With the list of url's in column B starting in B1. Enter this formula in C1:

=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A$1:A$5))))

Copy down as needed. Any matches found will return a value greater than 0.
Zero means no matches found.

However, this in and of itself will not "flag" the url's in column A. You
could use conditional formatting to actually "mark" any matches.

Select the url's in column A.
Goto FormatConditional Formatting
Formula is: =OR(ISNUMBER(SEARCH($B$1:$B$4,A1)))
Select a fill color or whatever style you would like.
OK out

Biff

wrote in message
oups.com...
Hi,
I'm trying to flag records that CONTAIN any of the strings in another
range. My purpose, in this specific case, is to examine a range of
website URLs and to remove any URLs belonging to particular domains.
For example:

ColumnA: List of URLS
www.sports.cnn.com/basketball.htm
www.aaa.baddomain.com/trouble.htm
www.groups.google.com/excel/
www.nothanksdomain.com
http://www.trouble.dontwantdomain.or...ouble/go/away/

ColumnB: List of domains to suppress
baddomain.com
nogooddomain.net
dontwantdomain.org
nothanksdomain.com

ColumnC: Result
I'm looking for a formulae that identifies if there is a LIKE match
between ColumnA and ColumnB and outputs TRUE/FALSE (or similar).

Thanks
Dean

Hi,
THanks for that. I followed the instructions and all results are coming
up 0. Would you mind checking the formula?

Thanks for your help
Dean

Hi,
searching for elements of the forumula you gave I found the following
on the web that is working for me:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$4,B1)))),"Found","Not Found")

Thanks for getting me there!
Dean