Search Word
 

http://cjoint.com/data/bguHFjhv8r.htm

My problem is attached on the above link. Anyone can help? I have been
looking for solutions for long....to make a simple search engine.

Also, can I make Column C update automatically (show Found/NOT Found) when I
keep adding new TargetNames on Column B.

Appreciate any help from you!!
THANK YOU VERY MUCH!!!

Search Word
 

First
Put this formula in cell C2 and copy it down as far as you need.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0,"FOUND","NOT FOUND")

Second
Create your list of forbidden words in a blank column. For this example, I
started my list in Cell E2 on Sheet2.

Then, InsertNameCreate
Name in Workbook: CheckList
Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
Click [OK]

That created a dynamic range name called CheckList that automatically
resizes to accomodate the number of items in the list beginning in cell E2 on
Sheet2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"guy" wrote:

http://cjoint.com/data/bguHFjhv8r.htm

My problem is attached on the above link. Anyone can help? I have been
looking for solutions for long....to make a simple search engine.

Also, can I make Column C update automatically (show Found/NOT Found) when I
keep adding new TargetNames on Column B.

Appreciate any help from you!!
THANK YOU VERY MUCH!!!

Search Word
 

Minor correction...
The formula for C2 and below should be:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),"FOUND","NOT FOUND")

(I wasn't paying good attention when I pasted the wrong version in my post)

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First
Put this formula in cell C2 and copy it down as far as you need.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0,"FOUND","NOT FOUND")

Second
Create your list of forbidden words in a blank column. For this example, I
started my list in Cell E2 on Sheet2.

Then, InsertNameCreate
Name in Workbook: CheckList
Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
Click [OK]

That created a dynamic range name called CheckList that automatically
resizes to accomodate the number of items in the list beginning in cell E2 on
Sheet2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"guy" wrote:

http://cjoint.com/data/bguHFjhv8r.htm

My problem is attached on the above link. Anyone can help? I have been
looking for solutions for long....to make a simple search engine.

Also, can I make Column C update automatically (show Found/NOT Found) when I
keep adding new TargetNames on Column B.

Appreciate any help from you!!
THANK YOU VERY MUCH!!!

Search Word
 

It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
it seems that they can produce the same result.
But I doubt how formula (A) works.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied to
get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If so,
the result will always be zero as long as there exists one unmatched word on
the checklist.
Sorry, I am really confused...I have searched a number of websites but none
of them supply me appropriate explanations...Can you explain this strange
thing? Please help!! Thanks!! (coz i not only want to solve the problem, but
also want to know how and why it is solved....to learn things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this newsgroup.)

"Ron Coderre" ...
Minor correction...
The formula for C2 and below should be:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),"FOUND","NOT FOUND")

(I wasn't paying good attention when I pasted the wrong version in my
post)

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First
Put this formula in cell C2 and copy it down as far as you need.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0,"FOUND","NOT FOUND")

Second
Create your list of forbidden words in a blank column. For this example,
I
started my list in Cell E2 on Sheet2.

Then, InsertNameCreate
Name in Workbook: CheckList
Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
Click [OK]

That created a dynamic range name called CheckList that automatically
resizes to accomodate the number of items in the list beginning in cell
E2 on
Sheet2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"guy" wrote:

http://cjoint.com/data/bguHFjhv8r.htm

My problem is attached on the above link. Anyone can help? I have been
looking for solutions for long....to make a simple search engine.

Also, can I make Column C update automatically (show Found/NOT Found)
when I
keep adding new TargetNames on Column B.

Appreciate any help from you!!
THANK YOU VERY MUCH!!!

Search Word
 

Revised:

It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
(B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0
it seems that they can produce the same result.
But I doubt how formula (A) and (B) work.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied to
get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If so,
the result will always be zero as long as there exists one unmatched word on
the checklist.
Sorry, I am really confused...I have searched a number of websites but none
of them supply me appropriate explanations...Can you explain this strange
thing? Please help!! Thanks!! (coz i not only want to solve the problem, but
also want to know how and why it is solved....to learn things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this newsgroup.)

"guy" bl...
It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
it seems that they can produce the same result.
But I doubt how formula (A) works.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied
to get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If
so, the result will always be zero as long as there exists one unmatched
word on the checklist.
Sorry, I am really confused...I have searched a number of websites but
none of them supply me appropriate explanations...Can you explain this
strange thing? Please help!! Thanks!! (coz i not only want to solve the
problem, but also want to know how and why it is solved....to learn
things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this newsgroup.)

"Ron Coderre" ...
Minor correction...
The formula for C2 and below should be:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),"FOUND","NOT FOUND")

(I wasn't paying good attention when I pasted the wrong version in my
post)

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First
Put this formula in cell C2 and copy it down as far as you need.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0,"FOUND","NOT
FOUND")

Second
Create your list of forbidden words in a blank column. For this example,
I
started my list in Cell E2 on Sheet2.

Then, InsertNameCreate
Name in Workbook: CheckList
Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
Click [OK]

That created a dynamic range name called CheckList that automatically
resizes to accomodate the number of items in the list beginning in cell
E2 on
Sheet2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"guy" wrote:

http://cjoint.com/data/bguHFjhv8r.htm

My problem is attached on the above link. Anyone can help? I have been
looking for solutions for long....to make a simple search engine.

Also, can I make Column C update automatically (show Found/NOT Found)
when I
keep adding new TargetNames on Column B.

Appreciate any help from you!!
THANK YOU VERY MUCH!!!

Search Word
 

Regarding:
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
(B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0
it seems that they can produce the same result.

You're correct...both formulas return the same values. I replaced formula(B)
with formula(A) because formula(A) is more efficient.

Regarding:
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied to
get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
muliplied first, then summed to get 1).

I'm not sure I understand the question. Case 1 doesn't multiply those values
(1,0,0,0)...it adds them up. Case 2 does multiply the two arrays
(1,0,0,0)*(1,0,0,0), which resolve to (1,0,0,0)....the same as Case 1. So,
you can see that multiplying by 1 and testing for 0 is unnecessary. Case 1
is the better formula.

And, finally:
BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Answer: Yes!

Does that answer your questions?

***********
Regards,
Ron

XL2002, WinXP-Pro


"guy" wrote:

Revised:

It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
(B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0
it seems that they can produce the same result.
But I doubt how formula (A) and (B) work.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied to
get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If so,
the result will always be zero as long as there exists one unmatched word on
the checklist.
Sorry, I am really confused...I have searched a number of websites but none
of them supply me appropriate explanations...Can you explain this strange
thing? Please help!! Thanks!! (coz i not only want to solve the problem, but
also want to know how and why it is solved....to learn things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this newsgroup.)

"guy" ¼¶¼g©ó¶l¥ó·s»D:uqqmL44EGHA.1088@tk2msftn gp13.phx.gbl...
It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
it seems that they can produce the same result.
But I doubt how formula (A) works.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied
to get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If
so, the result will always be zero as long as there exists one unmatched
word on the checklist.
Sorry, I am really confused...I have searched a number of websites but
none of them supply me appropriate explanations...Can you explain this
strange thing? Please help!! Thanks!! (coz i not only want to solve the
problem, but also want to know how and why it is solved....to learn
things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this newsgroup.)

"Ron Coderre" ...
Minor correction...
The formula for C2 and below should be:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),"FOUND","NOT FOUND")

(I wasn't paying good attention when I pasted the wrong version in my
post)

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First
Put this formula in cell C2 and copy it down as far as you need.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0,"FOUND","NOT
FOUND")

Second
Create your list of forbidden words in a blank column. For this example,
I
started my list in Cell E2 on Sheet2.

Then, InsertNameCreate
Name in Workbook: CheckList
Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
Click [OK]

That created a dynamic range name called CheckList that automatically
resizes to accomodate the number of items in the list beginning in cell
E2 on
Sheet2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"guy" wrote:

http://cjoint.com/data/bguHFjhv8r.htm

My problem is attached on the above link. Anyone can help? I have been
looking for solutions for long....to make a simple search engine.

Also, can I make Column C update automatically (show Found/NOT Found)
when I
keep adding new TargetNames on Column B.

Appreciate any help from you!!
THANK YOU VERY MUCH!!!

Search Word
 

"Ron Coderre" ...
Regarding:
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
(B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0
it seems that they can produce the same result.

You're correct...both formulas return the same values. I replaced
formula(B)
with formula(A) because formula(A) is more efficient.

Regarding:
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied
to
get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers
are
muliplied first, then summed to get 1).

I'm not sure I understand the question. Case 1 doesn't multiply those
values
(1,0,0,0)...it adds them up.
..
.....YES, YOU ARE RIGHT. I MADE A MISTAKE HERE...

Case 2 does multiply the two arrays
(1,0,0,0)*(1,0,0,0), which resolve to (1,0,0,0)....the same as Case 1. So,
you can see that multiplying by 1 and testing for 0 is unnecessary. Case
1
is the better formula.

And, finally:
BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Answer: Yes!

Does that answer your questions?

......YES, ALL IS CLEAR NOW. THANKS A LOT!!


***********
Regards,
Ron

XL2002, WinXP-Pro


"guy" wrote:

Revised:

It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
(B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0
it seems that they can produce the same result.
But I doubt how formula (A) and (B) work.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied
to
get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers
are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If
so,
the result will always be zero as long as there exists one unmatched word
on
the checklist.
Sorry, I am really confused...I have searched a number of websites but
none
of them supply me appropriate explanations...Can you explain this strange
thing? Please help!! Thanks!! (coz i not only want to solve the problem,
but
also want to know how and why it is solved....to learn things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this newsgroup.)

"guy" hx.gbl...

It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
it seems that they can produce the same result.
But I doubt how formula (A) works.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are
multiplied
to get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers
are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If
so, the result will always be zero as long as there exists one
unmatched
word on the checklist.
Sorry, I am really confused...I have searched a number of websites but
none of them supply me appropriate explanations...Can you explain this
strange thing? Please help!! Thanks!! (coz i not only want to solve the
problem, but also want to know how and why it is solved....to learn
things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this
newsgroup.)

"Ron Coderre" ...

Minor correction...
The formula for C2 and below should be:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),"FOUND","NOT FOUND")

(I wasn't paying good attention when I pasted the wrong version in my
post)

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First
Put this formula in cell C2 and copy it down as far as you need.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0,"FOUND","NOT
FOUND")

Second
Create your list of forbidden words in a blank column. For this
example,
I
started my list in Cell E2 on Sheet2.

Then, InsertNameCreate
Name in Workbook: CheckList
Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
Click [OK]

That created a dynamic range name called CheckList that automatically
resizes to accomodate the number of items in the list beginning in
cell
E2 on
Sheet2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"guy" wrote:

http://cjoint.com/data/bguHFjhv8r.htm

My problem is attached on the above link. Anyone can help? I have
been
looking for solutions for long....to make a simple search engine.

Also, can I make Column C update automatically (show Found/NOT
Found)
when I
keep adding new TargetNames on Column B.

Appreciate any help from you!!
THANK YOU VERY MUCH!!!