Comparing text in columns

[bird_222]

I think I may be asking alot here, but here it goes. I need a formula
that will return 'true' if ANY of the words in one column are in the
second column. I plan on using this 'true' value in an 'if' function.

For example (the formula would be in column C):

Column A Column B Column C
Wile E. Coyote coyote true
Wile E. Coyote Wile true

I hope my example is clear. Can someone provide some guidance?

TIA

 

[bird_222]

I think I may be asking alot here, but here it goes.  I need a formula
that will return 'true' if ANY of the words in one column are in the
second column.  I plan on using this 'true' value in an 'if' function.

For example (the formula would be in column C):

Column A               Column B          Column C
Wile E. Coyote         coyote                true
Wile E. Coyote          Wile                  true

I hope my example is clear.   Can someone provide some guidance?

TIA

I need to shift gears here but unfortunately I can't edit my post. I
want something like a 'vlookup' that matches any of the words in
column A with column B and returns column B. Something like this:

=vlookup('any word in column A',column B,1,false)

 

[Claus Busch]

Am Fri, 15 Apr 2011 09:19:11 -0700 (PDT) schrieb (e-mail address removed):

For example (the formula would be in column C):

Column A Column B Column C
Wile E. Coyote coyote true
Wile E. Coyote Wile true

in C1: =ISNUMBER(SEARCH(B1,A1))


Regards
Claus Busch

 

[Ron Rosenfeld]

I need to shift gears here but unfortunately I can't edit my post. I
want something like a 'vlookup' that matches any of the words in
column A with column B and returns column B. Something like this:

=vlookup('any word in column A',column B,1,false)

It is not clear what you want to match. Claus's response will return True if any of the words in A1 are found in B1 and can be easily modified to return the word, or FALSE if the word is not there. Something like:

=if(ISNUMBER(SEARCH(B1,A1)),B1)

If you want to know if ANY word in column B (all the cells) matches ANY word in column A (all the cells), you will have multiple matches. How do you want to handle that?

Perhaps you only want to compare the phrase in A1 with a list of words in column B, returning either the word in column B, or FALSE? If that is the case, it makes more sense, but you still have to decide what you want to do in the event that multiple words in the list in column B are included in the Phrase in A1.

 

[bird_222]

It is not clear what you want to match.  Claus's response will return True if any of the words in A1 are found in B1 and can be easily modified toreturn the word, or FALSE if the word is not there.  Something like:

=if(ISNUMBER(SEARCH(B1,A1)),B1)

If you want to know if ANY word in column B (all the cells) matches ANY word in column A (all the cells), you will have multiple matches.  How do you want to handle that?

Perhaps you only want to compare the phrase in A1 with a list of words incolumn B, returning either the word in column B, or FALSE?  If that is the case, it makes more sense, but you still have to decide what you want todo in the event that multiple words in the list in column B are included in the Phrase in A1.

I just want to compare the phrase in one cell (A1 for example) to a
range of cells in column B (for example B1:B10) and wherever the match
is found return the text in the B cell. With the list I am using
there shouldn't be any duplicates.

For example

Cell A1 Column B Cell C1
(has formula)
Wile E. Coyote apple coyote
(the match was found in B5 and I want the formula to put the text in
the match cell here)
blue
cherry
gun
coyote

 

[bird_222]

I just want to compare the phrase in one cell (A1 for example) to a
range of cells in column B (for example B1:B10) and wherever the match
is found return the text in the B cell.  With the list I am using
there shouldn't be any duplicates.

For example

Cell A1                               Column B                 Cell C1
(has formula)
Wile E. Coyote                    apple                        coyote
(the match was found in B5 and I want the formula to put the text in
the match cell here)
                                          blue
                                          cherry
                                          gun
                                          coyote

Well my sentence wrapped around. I want cell C1 that contains the
formula to display the text in whatever cell in column B where the
match occurred.

 

[Ron Rosenfeld]

I just want to compare the phrase in one cell (A1 for example) to a
range of cells in column B (for example B1:B10) and wherever the match
is found return the text in the B cell. With the list I am using
there shouldn't be any duplicates.

That's much more clear.

If you don't mind the possibility of words contained within other words; in other words:

A B C
blueberry apple berry
berry


Then you can use one of these:

WordList is a contiguous array (no blanks) of your words in column B.

These formulas must be **array-entered**:

For Excel 2007 or later:

=IFERROR(INDEX(WordList,MATCH(TRUE,ISNUMBER(SEARCH(WordList,A1)),0)),"")

For earlier versions, which do not have the IFERROR function:

=IF(ISNA(INDEX(WordList,MATCH(TRUE,
ISNUMBER(SEARCH(WordList,A1)),0))),"",
INDEX(WordList,MATCH(TRUE,
ISNUMBER(SEARCH(WordList,A1)),0)))

----------------------------------------

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.

---------------------------------------------------

If you do not want "blue" to match "blueberry", then the easiest solution will be with a VBA User Defined Function. If your words in the column A cells might not start/end with a letter/digit/underscore, the pattern in the UDF may need some modification.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=MatchWord(A1, WordList)

in some cell.

======================================
Option Explicit
Function MatchWord(Phrase As String, WordList As Range) As String
Dim re As Object, mc As Object
Dim sPat As String
Dim c As Range
Set re = CreateObject("vbscript.regexp")

sPat = "\b("
For Each c In WordList
If Len(c.Text) > 0 Then sPat = sPat & c.Text & "|"
Next c
sPat = Left(sPat, Len(sPat) - 1) & ")\b"

With re
.Global = True
.Pattern = sPat
.ignorecase = True
End With

If re.test(Phrase) Then
Set mc = re.Execute(Phrase)
MatchWord = mc(0)
End If

End Function
=============================

 

[bird_222]

That's much more clear.

If you don't mind the possibility of words contained within other words; in other words:

    A           B               C
blueberry       apple                 berry
                     berry

Then you can use one of these:

WordList is a contiguous array (no blanks) of your words in column B.

These formulas must be **array-entered**:

For Excel 2007 or later:

=IFERROR(INDEX(WordList,MATCH(TRUE,ISNUMBER(SEARCH(WordList,A1)),0)),"")

For earlier versions, which do not have the IFERROR function:

=IF(ISNA(INDEX(WordList,MATCH(TRUE,
ISNUMBER(SEARCH(WordList,A1)),0))),"",
INDEX(WordList,MATCH(TRUE,
ISNUMBER(SEARCH(WordList,A1)),0)))

----------------------------------------

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.

---------------------------------------------------

If you do not want "blue" to match "blueberry", then the easiest solutionwill be with a VBA User Defined Function.  If your words in the column Acells might not start/end with a letter/digit/underscore, the pattern in the UDF may need some modification.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=MatchWord(A1, WordList)

 in some cell.

======================================
Option Explicit
Function MatchWord(Phrase As String, WordList As Range) As String
    Dim re As Object, mc As Object
    Dim sPat As String
    Dim c As Range
Set re = CreateObject("vbscript.regexp")

sPat = "\b("
For Each c In WordList
    If Len(c.Text) > 0 Then sPat = sPat & c.Text & "|"
Next c
    sPat = Left(sPat, Len(sPat) - 1) & ")\b"

With re
    .Global = True
    .Pattern = sPat
    .ignorecase = True
End With

If re.test(Phrase) Then
    Set mc = re.Execute(Phrase)
    MatchWord = mc(0)
End If

End Function
=============================

Thanks How can I modify this to give the text exactly 2 columns (or
whatever number of columns) over from the match? Like how 'vlookup'
works. Is it possible to modify this to deal with blank cells or will
it get way too complicated?

 

[bird_222]

Thanks  How can I modify this to give the text exactly 2 columns (or
whatever number of columns) over from the match?  Like how 'vlookup'
works.  Is it possible to modify this to deal with blank cells or will
it get way too complicated?

This formula
{=IF(ISNA(INDEX(c2:c6,MATCH(TRUE,ISNUMBER(SEARCH(c2:c6,A1)),
0))),"",INDEX(c2:c6,MATCH(TRUE,ISNUMBER(SEARCH(c2:c6,A1)),0)))}
doesn't seem to work in Excel 2010. I am testing this on Excel 2010,
but it will be used on an older version of Excel.

 

[Ron Rosenfeld]

Thanks How can I modify this to give the text exactly 2 columns (or
whatever number of columns) over from the match? Like how 'vlookup'
works. Is it possible to modify this to deal with blank cells or will
it get way too complicated?

Your column of words "WordList" is where ever you put it. If your phrases are in column A, and your list of words is in Column C, just use C1:Cn as the argument.

 

[bird_222]

Your column of words "WordList" is where ever you put it.  If your phrases are in column A, and your list of words is in Column C, just use C1:Cn as the argument.

This is what I mean. The phrase is in cell A1, the match is in cell
B6, the text I want to put in cell C1 (where the formula is) is in
cell D6 (two columns over from the match).

 

[bird_222]

What do you mean by "doesn't seem to work"?  That phrase is not very helpful as there are many ways a formula might fail.  Describing the failure can be helpful in determining what the problem might be.

I can think of no reason why that formula should not work in any version of Excel.  My guess is that you are either entering it incorrectly; or the environment is not exactly how you described it.  But unless you supplymore information, it is difficult to troubleshoot.

I notice that you have curly brackets on either side of your formula in your message.  How did they get into your message here?

I just put them there to show that I entered it as an array. I guess
I shouldn't have posted the formula like that. Well it seems to be
working now. Excel was giving me the standard 'there's a problem with
your formula' error before.

 

[Ron Rosenfeld]

I just put them there to show that I entered it as an array. I guess
I shouldn't have posted the formula like that. Well it seems to be
working now. Excel was giving me the standard 'there's a problem with
your formula' error before.

Since you didn't copy/paste your formula originally, it is hard to tell what was wrong; but most likely there was some type when you entered it initially.

 

[Ron Rosenfeld]

This is what I mean. The phrase is in cell A1, the match is in cell
B6, the text I want to put in cell C1 (where the formula is) is in
cell D6 (two columns over from the match).

If WordList is the list of words in column B as before; and MatchList is your "text to return" in column D (and it must have the same number of rows as does WordList) then this **array-entered** formula should do the trick:

=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(
WordList,A2)),0)),"",INDEX(MatchList,MATCH(
TRUE,ISNUMBER(SEARCH(WordList,A2)),0)))

Note that we merely change the "array" argument in the INDEX function to point to the array from which you want to return the answer.

This, obviously, has the same limitation as before with regard to "whole words", but I guess that is not a problem for you.

 

[bird_222]

If WordList is the list of words in column B as before; and MatchList is your "text to return" in column D (and it must have the same number of rowsas does WordList) then this **array-entered** formula should do the trick:

=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(
WordList,A2)),0)),"",INDEX(MatchList,MATCH(
TRUE,ISNUMBER(SEARCH(WordList,A2)),0)))

Note that we merely change the "array" argument in the INDEX function to point to the array from which you want to return the answer.

This, obviously, has the same limitation as before with regard to "whole words", but I guess that is not a problem for you.

Thanks for all your help! How can this be modified to deal with blank
cells in the 'phrase list' column?

 

[Ron Rosenfeld]

Thanks for all your help! How can this be modified to deal with blank
cells in the 'phrase list' column?

What happened when you tried it?

If you entered it correctly, the formula should be returning a null string, which appears blank, if the cell in the Phrase List column is empty.

 

[bird_222]

What happened when you tried it?

If you entered it correctly, the formula should be returning a null string, which appears blank, if the cell in the Phrase List column is empty.

It works fine on the test data. Could you explain what this formula
is doing?

 

[Ron Rosenfeld]

It works fine on the test data. Could you explain what this formula
is doing?

Refer also to the Evaluate Formula wizard, and the HELP files

=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(
WordList,A2)),0)),"",INDEX(MatchList,MATCH(
TRUE,ISNUMBER(SEARCH(WordList,A2)),0)))


SEARCH(WordList,A2)

Searches each entry in WordList to see if it can be found in Cell A2. If it is found, it will return a number indicating the position. If it is not found, it will return a #VALUE! error . So if entry number three in WordList was found in A2; this function would return the array: {#VALUE!;#VALUE!;4;#VALUE!;VALUE!} The 4 represents the position of the 4th word in WordList in the Phrase in A2.

Nest that result in the ISNUMBER function:

ISNUMBER( {#VALUE!;#VALUE!;4;#VALUE!;VALUE!} ): Returns {FALSE;FALSE;TRUE;FALSE;FALSE}

The MATCH function, will then tell us where TRUE (actually, the first TRUE if there is more than one) is located:

MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE},0) --> 3

3 is then used in the INDEX function to return the appropriate entry from MatchList.

Using the Evaluate Formula dialog, you should be able to figure out the rest. Post back if there is something specific you do not understand.

 

[bird_222]

Refer also to the Evaluate Formula wizard, and the HELP files

=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(
WordList,A2)),0)),"",INDEX(MatchList,MATCH(
TRUE,ISNUMBER(SEARCH(WordList,A2)),0)))

SEARCH(WordList,A2)

Searches each entry in WordList to see if it can be found in Cell A2.  If it is found, it will return a number indicating the position.  If it is not found, it will return a #VALUE! error .  So if entry number three in WordList was found in A2; this function would return the array:  {#VALUE!;#VALUE!;4;#VALUE!;VALUE!}   The 4 represents the position of the 4th word in WordList in the Phrase in A2.

Nest that result in the ISNUMBER function:

ISNUMBER( {#VALUE!;#VALUE!;4;#VALUE!;VALUE!} ):  Returns {FALSE;FALSE;TRUE;FALSE;FALSE}

The MATCH function, will then tell us where TRUE (actually, the first TRUE if there is more than one) is located:

MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE},0) --> 3

3 is then used in the INDEX function to return the appropriate entry fromMatchList.

Using the Evaluate Formula dialog, you should be able to figure out the rest.  Post back if there is something specific you do not understand.

How can this be modified to deal with blank cells in the 'wordlist'
column?

Thanks!

 

[Ron Rosenfeld]

How can this be modified to deal with blank cells in the 'wordlist'
column?

Thanks!

I don't know how to modify the formula to do that. Perhaps some lurker can figure it out, if it is possible.

I would either use a VBA User Defined Function, or, if that is not allowed by your company, I would ensure that there are no blank cells in WordList.

The VBA UDF would be based on what I presented in my second response to you a few days ago. If that is what you want, you can either modify it yourself or, if you will present a full list of specifications in a single message, (as opposed to one new specification per message, which is what you have been doing), I would be happy to make the appropriate modifications.