This is a very interesting function and helped me a lot so far. My file though is a bit more complicated.
I have multiple info in one cell separated with ";" (example AD1; AD2; AD3) let's say that these are servers (File name SERVERS) and in each server, I have multiple applications.
I have now another file that has all the applications per server per line in excel (each line has one server one application. Filename: APPS).
I want to start from the file SERVERS to look up the servers that are in one cell find them in the second file APPS and bring all the applications also in one cell in the file SERVERS.
Any ideas here?
Thanks in advance
Worksheet Sheet2 contains the items and the corresponding applications.
Worksheet Sheet1 contains the concatenated items in column A and a formula in column B. The first argument in the UDF is the cell that contains the concatenated values, this cell reference is relative meaning it changes when you copy cell B2 and paste it to cells below.
The second argument is the lookup range and the third argument is the return range, both these arguments have absolute cell references.
'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
'Continue with next number
'Continue with next value
'Return values saved to result to worksheet
Lookup_concat = Trim(result)
Where to put the code?
You can use the code (see instructions below) in your workbook or download the example file.
Copy VBA code above.
Open VB Editor (Alt+F11) and select your workbook in the Project Explorer.
Click Insert on the menu.
Click Module to create a module.
Paste code to module1
Exit VB Editor
Note, save your workbook with file extension *.xlsm (macro enabled) to make sure you attach the code to your workbook.