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

b bb 1 Reputation point

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.

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)


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
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Emily Hua-MSFT 27,551 Reputation points

    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.

    2 people found this answer helpful.

  2. IlirU 6 Reputation points



    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

    1 person found this answer helpful.
    0 comments No comments