Excel formula to remove duplicates in a cell

[sharshra]

I have an output from TEXTJOIN, which has duplicates. I need unique values as the final output. I searched internet & found there are VBA UDFs to remove duplicates in a cell. But I want to achieve the same using Excel formulae.

Are there any options to remove duplicates in a cell using Excel formulae? Or is it possible to have an output from TEXTJOIN with unique values only? Please advise.

Example for illustration only-
Output from TEXTJOIN - abc, def, abc, xyz, bcd, cde, def, mno
Desired output without duplicates - abc, def, xyz, bcd, cde, mno

 

[kevin9999]

Let's say your list is in the range A1:A10. Try the formula:

=TEXTJOIN(",",TRUE,UNIQUE($A$1:$A$10))

 

[sharshra]

As mentioned in my post, I'm already using TEXTJOIN to get an output. Question is about removing duplicates in a single cell, NOT on range of cells.

 

[JoeMo]

Here's a UDF you can use until a worksheet functions formula is offered.
Note in the example below that ", " is used as the delimiter not just ",".

Book1
	A	B
1	abc, def, abc, xyz, bcd, cde, def, mno	abc, def, xyz, bcd, cde, mno
2	123, 456, 789	123, 456, 789
3	abc, def, xyz	abc, def, xyz
4
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=IF(A1="","",UniquesOnly(A1, ", "))

Function UniquesOnly(S As String, Delim As String) As String
Dim V As Variant, d As Object, i As Long, X As Variant
V = Split(S, Delim)
Set d = CreateObject("Scripting.dictionary")
For i = LBound(V) To UBound(V)
    If Not d.exists(V(i)) Then
        d.Add V(i), d.Count + 1
        X = X & Delim & V(i)
    End If
Next i
UniquesOnly = Mid(X, Len(Delim) + 1, Len(X) - Len(Delim) + 1)
End Function

 

[sharshra]

Thanks, Joe. I'm already using UDF, but wanted to achieve similar result using formulae.

 

[Rick Rothstein]

Assuming the text in your cell is never longer than 300 characters (make all three 300s larger by the same amount if it could be), then this formula seems to work...

=TEXTJOIN(", ",,UNIQUE(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",300)),SEQUENCE(1+LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*300,300))))

 

[sharshra]

Thanks, Rick. That works perfectly

Though I couldn't understand how it works, this is doing what I expected. Now trying to decipher it Brief explanation will be of great help

 

[Peter_SSs]

Are there any options to remove duplicates in a cell using Excel formulae?

Another option would be:

21 12 09.xlsm
	A	B
1	abc, def, abc, xyz, bcd, cde, def, mno	abc, def, xyz, bcd, cde, mno
No Dupes

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(", ",,UNIQUE(FILTERXML("<p><c>"&SUBSTITUTE(A1,", ","</c><c>")&"</c></p>","//c")))

Or is it possible to have an output from TEXTJOIN with unique values only?

If you shared the original TEXTJOIN formula and perhaps some sample data to see layout, that could be investigated.

 

[sharshra]

Thanks, Peter. As always, you have the solution

As in the case of Rick's solution, I couldn't figure out how the formula works. Scratching my head to understand it better. Any further insights on how it works will be greatly appreciated.

I'm happy that now there are 2 solutions Both works for me, but I can mark only one as solution

 

[Peter_SSs]

Any further insights on how it works will be greatly appreciated.

Not simple to explain the detail of the FILTERXML function (perhaps search the forum for more examples with FILTERXML) but it splits the delimited string into a list of individual values then UNIQUE removes the duplicates and TEXTJOIN puts what is left back into a delimited string.

In post 1 you seemed to also be asking if your original TEXTJOIN function could get the unique values directly but you didn't address that part of my last post.