If my string of a cell contains keyword matching from list then results must be that keyword only.

[Mehmud Khan]

Hi,

Good day!


I am stuck at an argument, I tried with multiple INDEX+MATCH+COUNTIF combination formulas but not getting the result.

If my string of a cell contains keyword matching from list then results must be that keyword only.

Else, if no keyword is match with master-list then result must be "STOP".

But additional condition is a cell can contained more than 1 strings separated by "+".

The below bold highlights is for reference purpose only.



Please check below examples and attachment.


Regards,
-Mehmud

 

[p45cal]

Try in cell BR2:

=TEXTJOIN("+",TRUE,FILTER(Classification!$G$3:$G$5,ISNUMBER(SEARCH(Classification!$G$3:$G$5,Sale!L2)),"STOP"))

and in cell BS2:

=TEXTJOIN("+",TRUE,FILTER(Classification!$G$3:$G$5,ISNUMBER(SEARCH(Classification!$G$3:$G$5,Sale!X2)),"STOP"))

both copied down.
Cell BR8 has the only difference from your expected results.

 

[Mehmud Khan]

Hi,

Thanks for your response.

Unfortunately, the TEXTJOIN fx is not working in MS 2016.

Any alternative option?

Regards,
Mehmud

 

[p45cal]

For Excel2016 I can only suggest a UDF:

Function FindCodes(ProdCode, Codes)
Dim result As String
For Each code In Codes.Cells
  If InStr(1, ProdCode.Value, code.Value, vbTextCompare) > 0 Then
    result = result & "+" & code.Value
  End If
Next code
If Len(result) > 0 Then result = Right(result, Len(result) - 1) Else result = "STOP"
FindCodes = result
End Function

Put the code in a standard code-module and use it thus on the sheet on row 2 somewhere:
=FindCodes(L2,'Classification'!$G$3:$G$5)
and copy down.

 

[Mehmud Khan]

Hi,

Thanks you very much for your response.

The function you provided is excellent and working very good.

I would appreciate, if you make correction in below highlighted 2 conditions;

1. As the keyword "GYM" is not listed in the classification, but last cell in BR column, the output is "LIQ" only which is incorrect. This must be "LIQ+STOP". [However, if product code is only "MH76GYML" without "+" then result output is correct i.e. "STOP", but not working with dual products]

2. The third product code in column X is "MH35SOLT+KQ71GASL", but results with your function is "GAS+SOL". However, the correct results should be "SOL+GAS", must be in sequence.




I hope I able to explain you.

I will be thanksful if you resolve my this issue as well.



Regards,
Mehmud

 

[p45cal]

Test:

Function FindCodes(ProdCode, Codes)
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
RawCodes = Codes.Value
'Filter out blank codes:
Count = -1
ReDim Codes(-1 To 0)
If IsArray(RawCodes) Then
  For Each RCode In RawCodes
    x = Application.Trim(RCode)
    If Len(x) > 0 Then
      Count = Count + 1
      ReDim Preserve Codes(0 To Count)
      Codes(Count) = x
    End If
  Next RCode
Else
  x = Application.Trim(RawCodes)
  If Len(x) > 0 Then
    ReDim Codes(0 To 0)
    Codes(0) = x
  End If
End If
x = ProdCode.Value
y = Split(x, "+")
If UBound(y) >= 0 And LBound(Codes) <> -1 Then
  ReDim pCodesOut(LBound(y) To UBound(y)) As String
  Count = LBound(y) - 1
  For Each pcode In y
    Count = Count + 1
    NoStop = False
    For Each code In Codes
      If InStr(1, pcode, code, vbTextCompare) > 0 Then
        If dict.Exists(code) Then
          NoStop = True
        Else
          dict.Add code, code
          If Len(pCodesOut(Count)) = 0 Then pCodesOut(Count) = code Else pCodesOut(Count) = pCodesOut(Count) & "," & code
        End If
      End If
    Next code
    If Len(pCodesOut(Count)) = 0 And Not NoStop Then pCodesOut(Count) = "STOP"
  Next pcode
  ReDim pCodesOut2(0 To 0) As String
  Count = LBound(y) - 1
  For Each pcodeOut In pCodesOut
    If Len(pcodeOut) > 0 Then
      Count = Count + 1
      ReDim Preserve pCodesOut2(0 To Count)
      pCodesOut2(Count) = pcodeOut
    End If
  Next pcodeOut
  FindCodes = Join(pCodesOut2, "+")
Else
  FindCodes = ""
End If
End Function

 

[p45cal]

Note the last message was updated just now.

 

[Mehmud Khan]

Hi,

The updated codes are excellent and 100% working without any challenges.

Thanks you very much.

This forum and active members are always give me positive responses.

Stay blessed.

Thanks and Regards,
-Mehmud