Find unique values in a range of cells but each cell has a list inside it

Question

Thank you for taking the time to read my question.

I'm wondering if there is a way, using an Excel function, to find all unique values across a range of cells BUT each cell has a comma separated list so each value in each cell should be considered, not each cell as a whole as the UNIQUE() function does.

Example
A1 = 1, 2, 3
A2 = 2, 3, 4
A3 = 3, 4, 5

I'd like my result to be 1, 2 ,3 ,4, 5

Using UNIQUE I get an array of the same values in A1:A3.

Is this possible or do I need to make a User Defined Function (which I'm hoping to avoid)

Thanks!

Answer 1

Hi @b bb

Currently, I find a way but not only using Excel formulas to achieve your needs.
Besides, in my opinion it would be better to split the original data into multiple columns first. Please check whether it is helpful to you.

• Select the whole column, right-click on it to choose "Get Data from Table/Range".
  
• Go to Home tab > Transform group > Split Column > By Delimiter.
  
• Press Ctrl key to select all columns, then go to Transform tab > Any Column group > Unpivot Columns.
  
• Select the first colmun of "Title" to remove.
  
• Go to Home tab > Reduce Rows group > Remove Rows > Remove Duplicates.
  
• Then you can click File > Close & Load To, put the relusts to a new worksheet.
  

If you want the result in one cell, you can use TEXTJOIN function.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Hi,

You can use below formula (see the printscreen)

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

Hope this helps.

Regards, IlirU