How to determine the value?

  • Thread starter Thread starter Eric
  • Start date Start date
E

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
 
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
 
Thank you very much for suggestions
Eric

Billy Liddel said:
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
Click to expand...
 
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
Click to expand...

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
 
Back
Top