Concatenate array elements based on search results of a single cell

[trishcollins]

I have a table with a column named "Description" and another column "Tags". I have a named range "tag_data" that lists all the tags I want to search for in the description, and then concatenate the tags into the Tag Column:

I am using a function I named ConcatenateArrary based on a ConcatenateIf function I found.

The "Tag_data" is the named_range is a list of tags in a single column of a table ("WLM", "Cloud", "Digicom", etc.). I use this named range to search the "description". If the tag is found in the description, I want it added to the "Tag" field.

For example the brief description says "Cloud connectivity for DND WLM", I want to say "Cloud, WLM" in the tag field

Here is the code I have so far, but it doesn't work.

Function ConcatenateArray(CriteriaRange As Range) As Variant
'Range is the cell, Variant is the Array with the tags
Dim xResult As String
arr1 = Range("Tag_Data")
Dim i As Long
Dim counti As Long
counti = 1
For i = LBound(arr1, 1) To UBound(arr1, 1)
If Search(arr1, CriteriaRange) Then
If counti = 1 Then
xResult = arr1(i, 1)
Else
xResult = xResult & ", " & arr1(i, 1)
End If
End If
Next i
ConcatenateArrary = xResult
Exit Function
End Function

 

[trishcollins]

Excellent! Works like a charm. You rock!

Trish

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.