Text Join - Return Unique Values Only

Mossad076 

The UNIQUE function is only available in Excel in Microsoft 365.

Here is a custom VBA function that you can copy into a module in the Visual Basic Editor:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
        ConcatenateRange As Range, Optional Separator As String = ",") As Variant
    Dim i As Long
    Dim strResult As String
    Dim dict As Object
    On Error GoTo ErrHandler
    Set dict = CreateObject("Scripting.Dictionary")
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            If ConcatenateRange.Cells(i).Value <> "" Then
                If Not dict.Exists(ConcatenateRange.Cells(i).Value) Then
                    dict.Add Key:=ConcatenateRange.Cells(i).Value, Item:=ConcatenateRange.Cells(i).Value
                End If
            End If
        End If
    Next i
    ConcatenateIf = Join(dict.keys, Separator)
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

The formula becomes

=ConcatenateIf(B2:B48,P1,C2:C48,", ")