Forum Discussion

Mossad076's avatar
Mossad076
Copper Contributor
May 31, 2021
Solved

Text Join - Return Unique Values Only

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

excel
  • HansVogelaar's avatar
    HansVogelaar
    May 31, 2021

    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.

6 Replies

    • Mossad076's avatar
      Mossad076
      Copper Contributor
      May 31, 2021

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

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 31, 2021

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

Resources