excel2007
I have a list of 100k text cells in sheet1, column A:
charles schwab layoffs
mcmaster carr
aig bonuses
slalom consulting
company reviews
cbeyond reviews
....

In sheet2, column A, I have a list of 50k company names:
coca cola
home depot
charles schwab
aig
....

I would like to put a "1" in sheet1, column b for every cell in column A
that contains a company name that can be found in sheet2, column A. If the
cell doesn't contain a company name, I would like to put a "0". For example,
"charles schwab layoffs" would get a "1" because it contains "charles schwab"
but "company reviews" would get a "0" because it doesn't contain a company
name.

I would just use the find function if I was only dealing with a few
companies but I am hoping that there is a formula that would be able to deal
with a large list of cells.

Don't have xl2007, but this should work ..

Assume data starts in A2 down in both Sheet1/2
First, set the calc mode to manual as it's very calc-intensive

In Sheet1,
Put in B2:
=IF(SUMPRODUCT(ISNUMBER(SEARCH(Sheet2!$A$2:$A$5000 0,A2))*(Sheet2!$A$2:$A$50000<""))0,1,0)
Copy down all the way. Press F9 to recalc. Adapt the range to suit.

Suggest you freeze/kill col B thereafter with an "in-place" copy n paste
special as values.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Paul" wrote:
excel2007
I have a list of 100k text cells in sheet1, column A:
charles schwab layoffs
mcmaster carr
aig bonuses
slalom consulting
company reviews
cbeyond reviews
...

In sheet2, column A, I have a list of 50k company names:
coca cola
home depot
charles schwab
aig
...

I would like to put a "1" in sheet1, column b for every cell in column A
that contains a company name that can be found in sheet2, column A. If the
cell doesn't contain a company name, I would like to put a "0". For example,
"charles schwab layoffs" would get a "1" because it contains "charles schwab"
but "company reviews" would get a "0" because it doesn't contain a company
name.

I would just use the find function if I was only dealing with a few
companies but I am hoping that there is a formula that would be able to deal
with a large list of cells.

Hi,

Try this array formula in sheet 1

=OR(ISNUMBER(SEARCH(Sheet2!$A$5:$A$8,A3,1)))*1

Please remember to confirm the formula by Ctrl+Shift+Enter and not Enter
alone

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Paul" wrote in message
...
excel2007
I have a list of 100k text cells in sheet1, column A:
charles schwab layoffs
mcmaster carr
aig bonuses
slalom consulting
company reviews
cbeyond reviews
...

In sheet2, column A, I have a list of 50k company names:
coca cola
home depot
charles schwab
aig
...

I would like to put a "1" in sheet1, column b for every cell in column A
that contains a company name that can be found in sheet2, column A. If
the
cell doesn't contain a company name, I would like to put a "0". For
example,
"charles schwab layoffs" would get a "1" because it contains "charles
schwab"
but "company reviews" would get a "0" because it doesn't contain a company
name.

I would just use the find function if I was only dealing with a few
companies but I am hoping that there is a formula that would be able to
deal
with a large list of cells.

Try this array formula** :

=--OR(COUNT(MATCH("*"&Sheet2!A$2:A$50000&"*",A2,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
excel2007
I have a list of 100k text cells in sheet1, column A:
charles schwab layoffs
mcmaster carr
aig bonuses
slalom consulting
company reviews
cbeyond reviews
...

In sheet2, column A, I have a list of 50k company names:
coca cola
home depot
charles schwab
aig
...

I would like to put a "1" in sheet1, column b for every cell in column A
that contains a company name that can be found in sheet2, column A. If
the
cell doesn't contain a company name, I would like to put a "0". For
example,
"charles schwab layoffs" would get a "1" because it contains "charles
schwab"
but "company reviews" would get a "0" because it doesn't contain a company
name.

I would just use the find function if I was only dealing with a few
companies but I am hoping that there is a formula that would be able to
deal
with a large list of cells.