If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 ctrlshift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula. --ron |
Thread Tools | |
Display Modes | |
|
|