CODEWITHSUNDEEP
excelvbaexcel-formula
Max
Max

Reputation: 940

Extracting duplicates from a list in a cell

I need help to extract duplicates from a comma separated list in a cell. I can do this the long way, but I need a short way if possible please.

Here are two examples:

E.g,

A1: 1,4,4,6,10,58   
Result in B1: 4,4

A2: 5,5,10,55,70,70,76
Result in B2: 5,5,70,70

Thanks for your help. *I have Excel 2016.

Upvotes: 1

Views: 130

Answers (1)

JvdV
JvdV

Reputation: 75840

Try, if one has TEXTJOIN (available from Excel 2019):

=TEXTJOIN(",",,FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[preceding::*=. or following::*=.]"))

In Excel 2016, you could create your own UDF:

Function Dupes(str As String) As String
    With Application
        Dupes = Join(.Transpose(.FilterXML("<t><s>" & Replace(str, ",", "</s><s>") & "</s></t>", "//s[preceding::*=. or following::*=.]")), ",")
    End With
End Function

As you can see, the syntax remains somewhat the same, as long as you got FILTERXML (available from Excel 2013).

Through the comments you mentioned you don't need to know the duplicates twice. Once is enough, therefor you could alter the XPATH syntax a little to:

=FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[preceding::*=. ][not(following::*=.)]")

You can do the same thing in your UDF which. If this sparks your interest, then you might like this post on SO for more ideas on how to use FILTERXML to rework delimited strings.

Upvotes: 3

Related Questions