SOLVED

Text Join - Return Unique Values Only

%3CLINGO-SUB%20id%3D%22lingo-sub-2400040%22%20slang%3D%22en-US%22%3EText%20Join%20-%20Return%20Unique%20Values%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2400040%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20a%20textjoin%20field%20that%20looks%20up%20a%20field%20in%20a%20column%20and%20matches%20it%20to%20a%20static%20field.%20Example%2C%20if%20Column%20B%20contains%20any%20instances%20of%20the%20word%20RED%20then%20return%20the%20values%20in%20Column%20C.%26nbsp%3B%20The%20issue%20i%20run%20into%20is%20duplicates%2C%20and%20I'd%20like%20to%20return%20unique%20answers.%20So%20if%20Red%20appears%2010%20times%20in%20column%20B%2C%20and%20Column%20C%20has%20some%20answers%20repeated%2C%20I%20only%20want%20one%20instance%20of%20the%20answer.%20Below%20is%20my%20current%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2CIF(%24P%241%3D%24B%242%3A%24B%2448%2C%24C%242%3A%24C%2448%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP1%20%3D%20Static%20field%3C%2FP%3E%3CP%3EB2%3AB48%20%3D%20Where%20I'd%20like%20to%20find%20a%20match%20for%20P1%3C%2FP%3E%3CP%3EC2%3AC48%20%3D%20The%20results%20I'd%20like%20to%20show%2C%20but%20only%20unique%20values.%20No%20blank%20rows%20(some%20fields%20have%20lists).%20For%20example%2C%20column%20C%20is%20a%20drop%20down%20list.%20Column%20D%20is%20manual%20entry%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2400040%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2400108%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20Join%20-%20Return%20Unique%20Values%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2400108%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1067220%22%20target%3D%22_blank%22%3E%40Mossad076%3C%2FA%3E%26nbsp%3BIf%20you%20are%20an%20MS365%20subscriber%2C%20try%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2CUNIQUE(IF(%24P%241%3D%24B%242%3A%24B%2448%2C%24C%242%3A%24C%2448%2C%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2401004%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20Join%20-%20Return%20Unique%20Values%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2401004%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20doesn't%20work%20and%20the%20screen%20displays%20%23NAME%3F%20I%20have%20office%202019%20Pro%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2401103%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20Join%20-%20Return%20Unique%20Values%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2401103%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1067220%22%20target%3D%22_blank%22%3E%40Mossad076%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20UNIQUE%20function%20is%20only%20available%20in%20Excel%20in%20Microsoft%20365.%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20custom%20VBA%20function%20that%20you%20can%20copy%20into%20a%20module%20in%20the%20Visual%20Basic%20Editor%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20ConcatenateIf(CriteriaRange%20As%20Range%2C%20Condition%20As%20Variant%2C%20_%0A%20%20%20%20%20%20%20%20ConcatenateRange%20As%20Range%2C%20Optional%20Separator%20As%20String%20%3D%20%22%2C%22)%20As%20Variant%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20strResult%20As%20String%0A%20%20%20%20Dim%20dict%20As%20Object%0A%20%20%20%20On%20Error%20GoTo%20ErrHandler%0A%20%20%20%20Set%20dict%20%3D%20CreateObject(%22Scripting.Dictionary%22)%0A%20%20%20%20If%20CriteriaRange.Count%20%26lt%3B%26gt%3B%20ConcatenateRange.Count%20Then%0A%20%20%20%20%20%20%20%20ConcatenateIf%20%3D%20CVErr(xlErrRef)%0A%20%20%20%20%20%20%20%20Exit%20Function%0A%20%20%20%20End%20If%0A%20%20%20%20For%20i%20%3D%201%20To%20CriteriaRange.Count%0A%20%20%20%20%20%20%20%20If%20CriteriaRange.Cells(i).Value%20%3D%20Condition%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20ConcatenateRange.Cells(i).Value%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20dict.Exists(ConcatenateRange.Cells(i).Value)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dict.Add%20Key%3A%3DConcatenateRange.Cells(i).Value%2C%20Item%3A%3DConcatenateRange.Cells(i).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0A%20%20%20%20ConcatenateIf%20%3D%20Join(dict.keys%2C%20Separator)%0A%20%20%20%20Exit%20Function%0AErrHandler%3A%0A%20%20%20%20ConcatenateIf%20%3D%20CVErr(xlErrValue)%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThe%20formula%20becomes%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DConcatenateIf(B2%3AB48%2CP1%2CC2%3AC48%2C%22%2C%20%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2401658%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20Join%20-%20Return%20Unique%20Values%20Only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2401658%22%20slang%3D%22en-US%22%3EThanks%20so%20much%2C%20however%2C%20now%20you%20are%20getting%20very%20complex%20for%20me%20haha.%20I%20setup%20%22Developer%20tab%22.%20Would%20you%20mind%20telling%20me%20what%20steps%20in%20VBA%20to%20create%20a%20file%3F%20I'll%20then%20copy%20and%20paste%20this.%20Also%2C%20since%20we%20are%20going%20into%20this%2C%20the%20C2%3AC48%20is%20just%20one%20column%2C%20i%20also%20need%20to%20return%20answers%20for%20D%20to%20M.%20They%20should%20each%20return%20their%20own%20answer.%20So%20column%20C%20returns%20answers%20in%20a%20cell%2C%20D%20in%20a%20different%20cell%2C%20etc.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hate%20to%20ask%20and%20really%20do%20appreciate%20it%20all!%3C%2FLINGO-BODY%3E
New Contributor

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

6 Replies

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

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

 

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

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

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!
best response confirmed by allyreckerman (Microsoft)
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.

This works. Thank you so much!