Need formula(s) to identify match between select words within text strings in 2 seperate cells

[freeb1893]

I'm trying to develop a formula where I can identify a match between text strings in separate cells, where the words inside the text string between the cells are not necessarily in the same order, and there may be other words in the text string that don't match.

There might be cases where the cells have the same name, but there are additional words in one of the two cells.

Here are some examples:

JOHN SMITH -----------------SMITH,JOHN A.
ARNOLD PALMER-------------PALMER JESSE ARNOLD
ALLEN STEVENS--------------STEVENS, JANE & ALLEN

Ideally, I would like a formula which identifies the 2 largest words in Cell A1, and then matches both those 2 largest words in Cell A1 to any of the words in Cell B1.

The words in Cell B1 may be separated by spaces or commas. (If we can only do a formula which looks at words separated only by spaces, I can remove the commas to make the formula work).

Any idea how to put this into a formula?

 

[ndsutherland]

This will return a match in the second column, but it assumes only two words in A1. Can you give some examples of what else might be in column A?

=INDEX($B$1:$B$3,MATCH(2,MMULT(ISNUMBER(SEARCH(CHOOSE({1,2},LEFT(A1,SEARCH(" ",A1)-1),RIGHT(A1,LEN(A1)-SEARCH(" ",A1))),$B$1:$B$3))*1,{1;1}),0))

 

[freeb1893]

It could be more than 2 words, really as many as several. But it will always be the two largest words in Column A that I need to find/match in Column B. Column A might also have periods or commas at times within the string. It won't always be separated by spaces (but I can remove the commas/periods if its necessary to make the formula work).

This will return a match in the second column, but it assumes only two words in A1. Can you give some examples of what else might be in column A?

=INDEX($B$1:$B$3,MATCH(2,MMULT(ISNUMBER(SEARCH(CHOOSE({1,2},LEFT(A1,SEARCH(" ",A1)-1),RIGHT(A1,LEN(A1)-SEARCH(" ",A1))),$B$1:$B$3))*1,{1;1}),0))

 

[XOR LX]

Hi.

Could you include perhaps half a dozen varied examples together with your expected results?

Regards

 

[freeb1893]

Sure! Here are some examples of where a match should be identified by the formula:

KIEUNGAN DAO-------------------DAO,TUAN & KIEU N
YA QUIN ZHANG-------------------ZHANG,YA Q
VICKI MONTGOMERY-------------------MONTGOMERY,VICKI L
JOHN R MARSHALL-------------------MARSHALL JOHN R TRUST
LAURIE T RODERIQUES-------------------RODERIQUES,LAURIE T
STEVE BOLLAR-------------------BOLLAR,STEVE B & DEBRA A

Here are some examples of where a match should not be identified by the formula:

DEBORAH J GOEDEL-------------------ROBERTS,RAYMOND R & MAJORIE J
HUGH BENSON-------------------RAY,LISA
LUZ ELENA MONTES ALVARADO-------------------ALVAREZ LUZ M E
STANLEY COTTA-------------------DICKEY,BRUCE
ORANGE LLC-------------------CITIMORTGAGE INC

Hi.

Could you include perhaps half a dozen varied examples together with your expected results?

Regards

 

[XOR LX]

Thanks, but I was hoping you could provide some examples in which your condition that the formula take into consideration the two largest words only is a factor.

Could you post a few such cases, e.g. in which taking two arbitrary words (as opposed to the two largest) might lead to undesired results?

Also, I thought we were matching against any of the words in column B? In all of your examples it seems rather fortunate that the matching word is in the exact same row.

Regards

 

[freeb1893]

No I'm going to be matching to only the words in the adjacent cell in Column B, such as B1 to A1, B2 to A2, etc. The matching value will be in the exact same row.

Below would be some examples where if we matched just 2 arbitrary words as opposed to the 2 largest words, where the match would be undesired:

BRIAN T WOLTER-------------------WOLTER LOUIS T
SHANNON KAY WARREN-------------------WARREN,DENISE KAY
J KAREN OLIVIER-------------------OLIVIER,AUDREY J

Thanks, but I was hoping you could provide some examples in which your condition that the formula take into consideration the two largest words only is a factor.

Could you post a few such cases, e.g. in which taking two arbitrary words (as opposed to the two largest) might lead to undesired results?

Also, I thought we were matching against any of the words in column B? In all of your examples it seems rather fortunate that the matching word is in the exact same row.

Regards

 

[XOR LX]

Sure! Here are some examples of where a match should be identified by the formula:

KIEUNGAN DAO-------------------DAO,TUAN & KIEU N

Thanks a lot. Much appreciated.

Can you just clarify the above example? This one is significantly different from the others in that, if words contained within others ("KIEU" within "KIEUNGAN") are to be considered, things are necessarily a lot more complicated.

Regards

 

[freeb1893]

Ahh yes... that one is a tough one... I forgot about that one. I would love to be able to catch that example by identifying words within words if its possible. But if a formula with that kind of ability proves too difficult to develop, having a formula that would catch the rest of those except that scenario would be an alternative that is better than nothing for me and my purposes.

Even a formula where instead of matching the full 2 largest words in the first text string, I match the first 4 characters of the 2 largest words in the first text string (Cell A1), to the first 4 characters to any words in the second text string (Cell B1), that might work for my purposes as well.

Thanks a lot. Much appreciated.

Can you just clarify the above example? This one is significantly different from the others in that, if words contained within others ("KIEU" within "KIEUNGAN") are to be considered, things are necessarily a lot more complicated.

Regards

 

[XOR LX]

I would love to be able to catch that example by identifying words within words if its possible.

I'm not sure how this is compatible with your other condition that only the two largest "words" be considered. After all, "KIEUNGAN" is not even a "word" within "KIEU".

That aside, try this array formula**:

=IF(COUNT(SEARCH(" "&INDEX(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),LEN(A1))),N(IF(1,MATCH(LARGE(LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),LEN(A1)))),{1,2}),LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),LEN(A1)))),0))))&" "," "&SUBSTITUTE(B1,","," ")&" "))=2,"Match","No Match")

though I don't agree with your expected result for "YA QUIN ZHANG" and "ZHANG,YA Q" (the two largest words in the former do not appear in the latter).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).