Search Word

G

guy

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!!!
 
R

Ron Coderre

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, Insert>Name>Create
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
 
R

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 said:
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, Insert>Name>Create
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 said:
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!!!
Click to expand...
Click to expand...
 
G

guy

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 said:
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 said:
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, Insert>Name>Create
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 said:
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!!!
Click to expand...
Click to expand...
Click to expand...
 
G

guy

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 said:
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 said:
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 said:
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, Insert>Name>Create
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


:

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!!!
Click to expand...
Click to expand...
Click to expand...
 
R

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.
Click to expand...

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).
Click to expand...

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?
Click to expand...

Answer: Yes!

Does that answer your questions?

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

XL2002, WinXP-Pro


guy said:
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 said:
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 said:
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


:

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, Insert>Name>Create
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


:

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!!!
Click to expand...
Click to expand...
Click to expand...
 
G

guy

Ron Coderre said:
Regarding:

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

Regarding:

I'm not sure I understand the question. Case 1 doesn't multiply those
values
(1,0,0,0)...it adds them up.
Click to expand...
..
.....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:

Answer: Yes!

Does that answer your questions?
Click to expand...

......YES, ALL IS CLEAR NOW. THANKS A LOT!!
***********
Regards,
Ron

XL2002, WinXP-Pro


guy said:
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 said:
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" <[email protected]> ???gcˆu?l¢Dˆu¡Ps?D:[email protected]...

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


:

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, Insert>Name>Create
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


:

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!!!
Click to expand...
Click to expand...
Click to expand...
 
Top