Match cell that contain common text

[nikhil0311]

I am trying to match column A & B. I am not looking for exact match. For example cell A4 & B4 here we have Korea as a common word. If found then output column which is column C should say yes else no.

Country 1	Country 2	Final Output
CCBSI-INTERNATIONAL HQ - INDIA	INDIA	Yes
CCBSI-INTERNATIONAL HQ - JAPAN		No
CCBSI-INTERNATIONAL HQ - KOREA	KOREA REPUBLIC OF	Yes
CCBSI-INTERNATIONAL HQ - NEW ZEALAND	NEW ZEALAND	Yes
CCBSI-INTERNATIONAL HQ - INDONESIA	AUSTRALIA	No

Any help would be much appreciated. Thanks all and stay safe

 

[ExcelGzh]

If you go to the Message Board and scroll down to the Hall of Fame Winners and go into that, you will see an article called "Alan's UDFs for the Fuzzy Match problem". Have a read of that. I think it might be the answer what you want - if reading it doesn't fry your brain! It's a bit complicated.

 

[Peter_SSs]

Korea as a common word. If found then output column which is column C should say yes else no

So what about these? Each has a common word so should they all be a 'yes' in column C?

20 04 02.xlsm
	A	B
2	CCBSI-INTERNATIONAL HQ - NEW ZEALAND	NEW CALEDONIA
3	CCBSI-INTERNATIONAL HQ - SOUTH KOREA	NORTH KOREA
4	CCBSI-INTERNATIONAL HQ - UNITED KINGDOM	UNITED STATES OF AMERICA
5	CCBSI-INTERNATIONAL HQ - SOUTH SUDAN	SUDAN
Common word

 

[nikhil0311]

So what about these? Each has a common word so should they all be a 'yes' in column C?

20 04 02.xlsm
	A	B
2	CCBSI-INTERNATIONAL HQ - NEW ZEALAND	NEW CALEDONIA
3	CCBSI-INTERNATIONAL HQ - SOUTH KOREA	NORTH KOREA
4	CCBSI-INTERNATIONAL HQ - UNITED KINGDOM	UNITED STATES OF AMERICA
5	CCBSI-INTERNATIONAL HQ - SOUTH SUDAN	SUDAN
Common word

Well it not as easy as i thought it would be. To answer your question, NO.

 

[nikhil0311]

Well it not as easy as i thought it would be. To answer your question, NO.

But lest assume if the Answe is Yes, what formula can we use?

 

[Peter_SSs]

But lest assume if the Answe is Yes, what formula can we use?

I would employ a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

I have provided two udfs. Neither is perfect but perhaps the second one may be closer to what you want?

The first looks for any "word" after the final "-" in column A in column B
The second looks for the whole exact text after the final "-" in column A in column B

Function MatchWord(sCCBSI As String, sCountry As String) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\b(" & Replace(Trim(Split(sCCBSI, "-")(2)), " ", "|") & ")\b"
  MatchWord = "No"
  If RX.test(sCountry) Then MatchWord = "Yes"
End Function


Function MtchWrd(sCCBSI As String, sCountry As String) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\b" & Trim(Split(sCCBSI, "-")(2)) & "\b"
  MtchWrd = "No"
  If RX.test(sCountry) Then MtchWrd = "Yes"
End Function

ikhil0311 2020-04-02 1.xlsm
	A	B	C	D
1	Country 1	Country 2	MatchWord	MtchWrd
2	CCBSI-INTERNATIONAL HQ - INDIA	INDIA	Yes	Yes
3	CCBSI-INTERNATIONAL HQ - JAPAN		No	No
4	CCBSI-INTERNATIONAL HQ - KOREA	KOREA REPUBLIC OF	Yes	Yes
5	CCBSI-INTERNATIONAL HQ - NEW ZEALAND	NEW ZEALAND	Yes	Yes
6	CCBSI-INTERNATIONAL HQ - INDONESIA	AUSTRALIA	No	No
7	CCBSI-INTERNATIONAL HQ - NEW ZEALAND	NEW CALEDONIA	Yes	No
8	CCBSI-INTERNATIONAL HQ - SOUTH KOREA	NORTH KOREA	Yes	No
9	CCBSI-INTERNATIONAL HQ - UNITED KINGDOM	UNITED STATES OF AMERICA	Yes	No
10	CCBSI-INTERNATIONAL HQ - SOUTH SUDAN	SUDAN	Yes	No
11	CCBSI-INTERNATIONAL HQ - SUDAN	SOUTH SUDAN	Yes	Yes
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=MatchWord(A2,B2)
D2:D11	D2	=MtchWrd(A2,B2)

 

[nikhil0311]

I would employ a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

I have provided two udfs. Neither is perfect but perhaps the second one may be closer to what you want?

The first looks for any "word" after the final "-" in column A in column B
The second looks for the whole exact text after the final "-" in column A in column B

Function MatchWord(sCCBSI As String, sCountry As String) As String
  Dim RX As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\b(" & Replace(Trim(Split(sCCBSI, "-")(2)), " ", "|") & ")\b"
  MatchWord = "No"
  If RX.test(sCountry) Then MatchWord = "Yes"
End Function


Function MtchWrd(sCCBSI As String, sCountry As String) As String
  Dim RX As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\b" & Trim(Split(sCCBSI, "-")(2)) & "\b"
  MtchWrd = "No"
  If RX.test(sCountry) Then MtchWrd = "Yes"
End Function

ikhil0311 2020-04-02 1.xlsm
	A	B	C	D
1	Country 1	Country 2	MatchWord	MtchWrd
2	CCBSI-INTERNATIONAL HQ - INDIA	INDIA	Yes	Yes
3	CCBSI-INTERNATIONAL HQ - JAPAN		No	No
4	CCBSI-INTERNATIONAL HQ - KOREA	KOREA REPUBLIC OF	Yes	Yes
5	CCBSI-INTERNATIONAL HQ - NEW ZEALAND	NEW ZEALAND	Yes	Yes
6	CCBSI-INTERNATIONAL HQ - INDONESIA	AUSTRALIA	No	No
7	CCBSI-INTERNATIONAL HQ - NEW ZEALAND	NEW CALEDONIA	Yes	No
8	CCBSI-INTERNATIONAL HQ - SOUTH KOREA	NORTH KOREA	Yes	No
9	CCBSI-INTERNATIONAL HQ - UNITED KINGDOM	UNITED STATES OF AMERICA	Yes	No
10	CCBSI-INTERNATIONAL HQ - SOUTH SUDAN	SUDAN	Yes	No
11	CCBSI-INTERNATIONAL HQ - SUDAN	SOUTH SUDAN	Yes	Yes
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=MatchWord(A2,B2)
D2:D11	D2	=MtchWrd(A2,B2)

Thanks a lot Peter. Appreciate your help!

 

[Peter_SSs]

Thanks a lot Peter. Appreciate your help!

You're welcome.