Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the value?
There are lists of text from cell A1 to A10 and from B1 to B20,
For example, under column A Mary John Peter .... Ann under column B John and Mary go to school by bus Jim eats apple in classroom .... Ann studies in library under column C, I would like to know whether the keywords under column A exists on any statement under column B or not, keyword "Mary" exists on the first statement, so it returns 1 in cell C1, but no any keyword exist on the second statement, so it returns 0 in cell C2, keyword "Ann" exists on the last statement, so it returns 1 in C20. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the value?
On Wed, 21 Apr 2010 14:36:01 -0700, Eric
wrote: There are lists of text from cell A1 to A10 and from B1 to B20, For example, under column A Mary John Peter ... Ann under column B John and Mary go to school by bus Jim eats apple in classroom ... Ann studies in library under column C, I would like to know whether the keywords under column A exists on any statement under column B or not, keyword "Mary" exists on the first statement, so it returns 1 in cell C1, but no any keyword exist on the second statement, so it returns 0 in cell C2, keyword "Ann" exists on the last statement, so it returns 1 in C20. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric If I understand you correctly, the following **array-entered** formula should do what you required. As written, it is case-sensitive. To make it case-INsensitive, change FIND to SEARCH. The formula does not check for whole words; so "Annabel is here" will also match, since Ann is part of Annabel. This formula must be **array-entered**: =--(MIN(FIND(IF($A$1:$A$20="",CHAR(1),$A$1:$A$20),B1& CHAR(1)&$A$1:$A$20))<=LEN(B1)) To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine best way to sum | Excel Discussion (Misc queries) | |||
How to determine the value - 18 Dec? | Excel Discussion (Misc queries) | |||
How to determine the value - 15 Dec? | Excel Discussion (Misc queries) | |||
How do I determine a y value for x ? | Charts and Charting in Excel | |||
pre determine value | Excel Discussion (Misc queries) |