May 30 2021 09:02 PM
May 30 2021 09:02 PM
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
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
May 31 2021 05:09 AM - edited May 31 2021 05:12 AM
It doesn't work and the screen displays #NAME? I have office 2019 Pro
May 31 2021 05:49 AM
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
May 31 2021 10:21 AM
May 31 2021 10:59 AMSolution
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:
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.