Lookup-multiple-particial values-in-one-cell

[asallame]

HELLO EVERY BODY

I’d like to complete vba formula with the expected result at attach file , i am sorry for my bad english language .

vba code

'Name the UDF and declare arguments and data types
Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)

'Dimension variables and declare data types
Dim i As Long, Result As String
Dim Search_strings, Value As Variant

'Split string using a delimiting character and return an array of values
Search_strings = Split(Search_string, ";")

'Iterate through values in array
For Each Value In Search_strings

'Iterate through from 1 to the number of cells in Search_in_col
For i = 1 To Search_in_col.Count

'Check if cell value is equal to value in variable Value
If Search_in_col.Cells(i, 1) = Value Then

'Save the corresponding return value to variable result
Result = Result & "," & Return_val_col.Cells(i, 1).Value
End If

'Continue with next number
Next i

'Continue with next value
Next Value

'Return values saved to result to worksheet
Lookup_concat = Trim(Result)

End Function


note
vba code is not mine

thank u

Lookup-multiple-particial values-in-one-cell - Copy.xlsm
	A	B	C	D	E	F	G	H
1
2		Servers	Applications
3		AD1,AD5,AD3	APP1
4		AD4	APP2
5		AD1	APP2
6		AD2	APP4
7		AD2	APP5
8		AD4,AD2	APP6
9		AD3	APP7
10		AD3	APP8
11		AD5	APP9
12
13
14
15
16
17
18
19		Servers	Applications			EXPECTED RESULTS WITH ALL MATCHING PARTIAL STRING OF LIST	FINAL EXPECTED RESULTS WITHOUT DUPLICATED
20		AD1;AD3	,APP2,APP7,APP8			APP1,APP2,APP1,APP7,APP8	APP1,APP2,APP7,APP8
21		AD4;AD5	,APP2,APP9			APP2,APP6,APP1,APP9	APP2,APP6,APP1,APP9
22
23
24
Sheet1

Cell Formulas
Range	Formula
C20:C21	C20	=Lookup_concat(B20,Sheet1!$B$3:$B$11,Sheet1!$C$3:$C$11)

 

[Anthony47]

Try this variant:

Function Lookup_concat(Search_string As String, Search_in_col As Range, Return_val_col As Range)
'Dimension variables and declare data types
Dim i As Long, Result As String
Dim Search_strings, Value As Variant
Dim ServerS, IApp()

'Split string using a delimiting character and return an array of values
Search_strings = Split(Search_string, ";")
ReDim IApp(1 To Return_val_col.Rows.Count)          'List of inserted Apps
'Iterate through values in array
For Each Value In Search_strings
'Iterate through from 1 to the number of cells in Search_in_col
    For i = 1 To Search_in_col.Count
        If Search_in_col.Cells(i, 1).Value <> "" Then
            ServerS = Split(Search_in_col.Cells(i, 1).Value, ",", , vbTextCompare)
            If Not IsError(Application.Match(Value, ServerS, False)) Then
                If IsError(Application.Match(Return_val_col.Cells(i, 1).Value, IApp, False)) Then
                    'Save the corresponding return value to variable result
                    Result = Result & "," & Return_val_col.Cells(i, 1).Value
                    IApp(i) = Return_val_col.Cells(i, 1).Value
                End If
            End If
        End If
    Next i
Next Value
'Return values saved to result to worksheet
Lookup_concat = Trim(Mid(Result, 2))
End Function

Bye

 

[Peter_SSs]

Another way

Function Lookup_concat(Search_string As String, Search_in_col As Range, Return_val_col As Range) As String
  Dim d As Object, RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long
 
  a = Search_in_col.Value
  b = Return_val_col.Value
  Set d = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\b(" & Replace(Search_string, ";", "|") & ")\b"
  For i = 1 To UBound(a)
    If RX.Test(a(i, 1)) Then d(b(i, 1)) = 1
  Next i
  If d.Count > 0 Then Lookup_concat = Join(d.Keys(), ",")
End Function

asallame 1.xlsm
	A	B	C
1
2		Servers	Applications
3		AD1,AD5,AD3	APP1
4		AD4	APP2
5		AD1	APP2
6		AD2	APP4
7		AD2	APP5
8		AD4,AD2	APP6
9		AD3	APP7
10		AD3	APP8
11		AD5	APP9
12
18
19		Servers	Applications
20		AD1;AD3	APP1,APP2,APP7,APP8
21		AD4;AD5	APP1,APP2,APP6,APP9
22
Sheet1

Cell Formulas
Range	Formula
C20:C21	C20	=Lookup_concat(B20,Sheet1!$B$3:$B$11,Sheet1!$C$3:$C$11)

If your function formulas and the table are in fact all on Sheet1 then of course you do not need the Sheet1 references in the formula. It would just be
=Lookup_concat(B20,$B$3:$B$11,$C$3:$C$11)

 

[asallame]

thank u it is really helped me
thank u so much Mr.peter

 

[asallame]

thank u it is really helped me
thank u so much Mr.anthony

 

[Peter_SSs]

You're welcome. Thanks for the follow up. Glad we could help.