1

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.

Max
  • 932
  • 1
  • 10
  • 20
  • 2
    any particular reason why you want the duplicates twice? What is the follow-up here? Out of curiousity... – JvdV Jul 26 '20 at 18:01
  • Sorry, I was away. It was not necessary, one of them could be enough too. both ways work for me. I didn't want to make it complicated. – Max Jul 26 '20 at 19:41
  • 1
    It won't really complicate the matter. See the update in the answer. Slight change of syntax. – JvdV Jul 26 '20 at 20:40
  • Thanks, again. I will use the new version then. – Max Jul 28 '20 at 04:55

1 Answers1

3

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70