Text Join - Return Unique Values Only

[Mossad076]

Hello,

I have a textjoin field that looks up a field in a column and matches it to a static field. Example, if Column B contains any instances of the word RED then return the values in Column C.  The issue i run into is duplicates, and I'd like to return unique answers. So if Red appears 10 times in column B, and Column C has some answers repeated, I only want one instance of the answer. Below is my current formula

=TEXTJOIN(", ",TRUE,IF($P$1=$B$2:$B$48,$C$2:$C$48,""))

P1 = Static field

B2:B48 = Where I'd like to find a match for P1

C2:C48 = The results I'd like to show, but only unique values. No blank rows (some fields have lists). For example, column C is a drop down list. Column D is manual entry

Thanks

[#2]

@Mossad076 If you are an MS365 subscriber, try this:

=TEXTJOIN(", ",TRUE,UNIQUE(IF($P$1=$B$2:$B$48,$C$2:$C$48,"")))

[#3]

It doesn't work and the screen displays #NAME? I have office 2019 Pro

[#4]

@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,", ")

[#5]

Thanks so much, however, now you are getting very complex for me haha. I setup "Developer tab". Would you mind telling me what steps in VBA to create a file? I'll then copy and paste this. Also, since we are going into this, the C2:C48 is just one column, i also need to return answers for D to M. They should each return their own answer. So column C returns answers in a cell, D in a different cell, etc.

I hate to ask and really do appreciate it all!

[#6 · Accepted solution]

@Mossad076 

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy/paste the code from my previous reply into the new module.

Switch back to Excel.

Create the following formula for column C:

=ConcatenateIf($B$2:$B$48,$P$1,C$2:C$48,", ")

This can be filled or copied to the right for columns D, E, etc.

Save the workbook as a macro-enabled workbook (*.xlsm)

Make sure that you allow macros when you open it.

[#7]

This works. Thank you so much!