Forum Discussion
Text Join - Return Unique Values Only
- May 31, 2021
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.
It doesn't work and the screen displays #NAME? I have office 2019 Pro
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,", ")
- Mossad076May 31, 2021Copper ContributorThanks 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!- HansVogelaarMay 31, 2021MVP
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.
- Mossad076May 31, 2021Copper ContributorThis works. Thank you so much!