ConcatenateUnique.bas

Function ConcatenateUnique(ByRef WhichRange As Range, _
        Optional ByVal Seperator As String = " ", _
        Optional ByVal Format As String = "@", _
        Optional ByVal CaseSensitive As Boolean = False) _
        As String

    Dim valArr() as Variant             'array with values from WhichRange
    Dim i as Long, j as Long            'iteration variables
    Dim CompMethod As VbCompareMethod   'compare method
    Dim dict as Object                  'Dictionary object
    
    'set compare method
    CompMethod = IIf(CaseSensitive,vbBinaryCompare,vbTextCompare)

    'put all values from range into array
    valArr = WhichRange.Value

    'create and setup Dictionary Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = CompMethod
  
    For i=1 To UBound(valArr,1)
        For j=1 to UBound(valArr,2)
            If Not valArr(i,j)=vbNullString And dict.Exists(valArr(i,j))=False Then
                dict.Add Format(valArr(i,j),Format), ""
            End If 
        Next j
    Next i

    'return value
    ConcatenateUnique = Join(dict.Keys,Seperator)

    Set dict = Nothing
End Function