| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting Excel whilst using the sumif formula to search for part of a word | Excel Discussion (Misc queries) | |||
| 1 word search | New Users to Excel | |||
| need formula to search column for a word and return another word | Excel Discussion (Misc queries) | |||
| how can I search a field for a particular word | Excel Worksheet Functions | |||
| How to Search for word in the Entire Workbook | Excel Discussion (Misc queries) | |||
Linear Mode
