1

I have the following setup (which is shared by a number of users through office 365 and changed frequently):

Setup image

I need to collect all unique items from a number of comma-separated lists in a range and write out unique values in rows.

Based on the example above, I need something like this:

Result image

I have managed to find a solution (I think it was in this forum, or a blog)

=FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(", ",,UNIQUE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",TRUE,B2:D4),",","</y><y>")&"</y></x>","//y"))),",","</y><y>")&"</y></x>","//y")

but unfortunately, this solution only works on desktop Excel and does not work with Excel 365 (online Excel). Is there anything I can do in Excel 365 to have the same result?

Improve this question
1
  • 1
    I see you are using TEXTJOIN, you may need to rewrite but TEXTSPLIT should let you get it into the column format.
    – gns100
    Commented Nov 7, 2022 at 19:01

1 Answer 1

Reset to default
1 
=UNIQUE(TRIM(TEXTSPLIT(TEXTJOIN(", ",,B2:D4),,",")))

enter image description here

Improve this answer
1
  • Exactly what I needed. And much better solution compared to XML filtering. Many thanks
    – Vijeke
    Commented Nov 9, 2022 at 8:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .