The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to extract unique distinct values from a single column and concatenate the result into one cell.
The following picture shows you 4 values in column B. Value "AA" has a duplicate in cell B5. Unique distinct values are all values except duplicates.
The first argument in the TEXTJOIN function lets you specify the delimiting character. The second argument if you want to ignore empty strings. The third argument is the values you want to concatenate.
The formula in cell D3 contains a User defined Function that extracts unique distinct values concatenated, you have the option to specify the delimiting character in the second argument.
The formula is a regular formula, however, you need to copy the code below and paste it to a code module in your workbook before you use it.
Function UniqConcat(rng As Range, str As String)
Dim ucoll As New Collection, Value As Variant, temp As String
On Error Resume Next
For Each Value In rng
If Len(Value) > 0 Then ucoll.Add Value, CStr(Value)
On Error GoTo 0
For Each Value In ucoll
temp = temp & Value & str
temp = Mid(temp, 1, Len(temp) - Len(str))
UniqConcat = temp
Where to put the code?
Press Alt + F11 to open the VB Editor.
Click on "Insert" on the menu.
Click on "Module".
Copy above code.
Paste to code module.
Exit VB Editor and return to Excel.
Note, save your workbook with file extension *.xlsm (macro-enabled) in order to keep the code.