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

1 Reputation point
2022-03-15T13:19:39.163+00:00

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!

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,659 questions

1. 27,551 Reputation points
2022-03-16T03:07:42.163+00:00

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.

2. 6 Reputation points
2022-03-20T09:34:28.62+00:00

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