How to determine the value?

[Eric]

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

 

[Billy Liddel]

Hi Eric,

The following returns one if the key word is on the same row.
=COUNTIF(B1,"*"&A1&"*")

However, you would then have to change the cell for John in the next row.
=COUNTIF(B1,"*"&A2&"*")

If you do not mind using VBA this User Defined Function will do the job more
easily.

'================================
Function CountKeyWord(ByRef rngText As Range, ByVal Key As String) As Integer
Dim c As Variant
Dim iCount As Integer
Dim sOpt As String

sOpt = "*"
Key = sOpt & Key & sOpt
For Each c In rngText
If c Like Key Then
iCount = iCount + 1
End If
Next c

CountKeyWord = iCount
End Function

'====================
If you have not used VBA before then follow these steps.

Press ALT + F11, Insert, Module
Copy the code between the lines into the module and Press ALT + Q to return
to the sheet.

In C1 enter the function
=countkeyword($B$1:$B$20,A1)
copy the function down to the last keyword.


HTH
Peter

 

[Eric]

Thank you very much for suggestions
Eric

Hi Eric,

The following returns one if the key word is on the same row.
=COUNTIF(B1,"*"&A1&"*")

However, you would then have to change the cell for John in the next row.
=COUNTIF(B1,"*"&A2&"*")

If you do not mind using VBA this User Defined Function will do the job more
easily.

'================================
Function CountKeyWord(ByRef rngText As Range, ByVal Key As String) As Integer
Dim c As Variant
Dim iCount As Integer
Dim sOpt As String

sOpt = "*"
Key = sOpt & Key & sOpt
For Each c In rngText
If c Like Key Then
iCount = iCount + 1
End If
Next c

CountKeyWord = iCount
End Function

'====================
If you have not used VBA before then follow these steps.

Press ALT + F11, Insert, Module
Copy the code between the lines into the module and Press ALT + Q to return
to the sheet.

In C1 enter the function
=countkeyword($B$1:$B$20,A1)
copy the function down to the last keyword.


HTH
Peter

 

[Ron Rosenfeld]

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