Try adding this UDF to your VB Editor (ALT+F11, Insert|Module)
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
Then in K5 enter formula:
=SUBSTITUTE(TRIM(aconcat(IF($E$2:$E$51=I5,$F$2:$F$51,"")," "))," ",", ")
confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.
Bookmarks