Comments on: UNIQUE function - quick way to find unique values in Excel

In the previous versions of Excel, getting a list of unique values was a hard challenge. The introduction of the UNIQUE function in Excel 365 has changed everything! Now, you don't need to be a formula expert to get unique values from a range, based on one or multiple criteria, and sort the results in alphabetical order. Continue reading →

Comments page 2. Total comments: 188

  1. I am analyzing the use of an online platform used by our company. I have sorted the numbers of users, their most recent entry into the platform, and how many "clicks" each user has made within a time period.

    I now need to determine how many unique days a user enters into the platform witching that time period.

    As an example; there are 5 users, Bob is the heaviest user with 1000 clicks in the past 14 days, and his most recent access was yesterday. Did Bob use it heavily for only one of those days, or did he access it 14 of those 14 days?

    I thought counting log-ins over that period would work, however, the platform punts out inactive users resulting in multiple daily log ins.

    What formula/feature would count how many of those past 14 days Bob accessed the platform?

    I think this is possible, but it certainly has eluded me so far. Ideas?

  2. Andrew 2011
    Betty. 2010
    Robert. 2015
    Andrew. 2017
    Steve 2019
    Andrew 2020

    From that how do I calculate that AStendrew won 3 times? Like below..

    Andrew 3
    Robert. 1
    Betty. 1
    Steve. 1

    Please say what formula?

  3. Aaah. I just realised iI hadn't managed to produce a unique list!

  4. Sorry I didn't explain it very well. I have a timesheet with days of the week and space to put start and finish times in for each job in a column next to the times. In between each day (alternating columns) is the space for the job number. I wish to extract the job number (the 6 digits previously shown) from the 7 columns (1 for each day). As you can see I managed to get a unique list (1 column for each of the first 3 days) but would like to have unique list from these into one list representing the list of jobs attended that week. I hope this explains it better.

  5. Hi. I have a basic UNIQUE formula but wish to combine the result from non-adjacent cells into 1 list. At the moment I get a column for each range.

    =SORT(UNIQUE(CHOOSE({1,2,2},D9:D40,G9:G40,J9:J40)))

    Result=

    0 0 0
    100000 100000 100000
    200000 100000 100000
    200000 300000 300000
    300000 100000 100000
    300000 200000 200000
    400000 200000 200000
    400000 300000 300000

    How can I get this (plus 4 more ranges) into 1 list of unique numbers?

    My skills are quite basic so hopefully there is a simple solution. Thank you

    1. Hi!
      Sorry, it's not quite clear what you are trying to achieve. Could you please describe it in more detail?

  6. I found this valuable post and wonder if anyone knows how to search a column and only return the unique values in the corresponding cell number in another column. For example, if column A, cells 1 thru 19 have 1111122222333334444... then B1=1 (B2 thru B5 must be blank), B6 =2 (B7 thru B10 must be blank), B11=3 (B12 thru B15 must be blank), and B16=4 (B17 thru B19 must be blank). Thanks in advance for your assistance.

    Ty

    1. Hi!
      To find the first occurrence of a value in a column, use this article's guidelines: How to find duplicates in Excel. Try to enter the following formula in the first cell and then copy it down along the column:

      =IF(COUNTIF($A$1:A1,A1)=1,A1,"")

      I hope I answered your question.

      1. Thanks for the guidance! This formula does exactly what I need... =IF(COUNTIF($A2:$A$19, $A2)>1, IF(COUNTIF($A$2:$A2, $A2)=1, A2,""), "")

        1. @Alexander... When I try to use the formula between two sheets, it only returns a value in the first cell of column B. Here is the modified formula

          =IF(COUNTIF($A2:$A$19, $A2)>1, IF(COUNTIF($A$2:$A2, $A2)=1, A2,""), "")

          Sheet1 A2 thru A19 have duplicates as stated in the initial post of this thread, the formula is entered in Sheet 2 cell A2 and copied from cell A3 to A19 by dragging from cell A2. Only cell A2 on Sheet 2 shows a value.

          If I use the following formula on a single sheet, column B returns only the first instance of the duplicate values in the corresponding cell.

          Do I need to use a different formula because I am referencing a separate sheet?

          1. @Alexander... Please disregard my previous post. I found the issue... The duplicates in Sheet1, Column A, were not formatted properly. I had 111122222221113333333444 instead of 111112223333334444. Thanks again!

            1. @Alexander I need to with the following scenario... In Sheet 1 Column A, the data is in the ordered format such as 111112223333334444. In Sheet 1 Column B there are instances when the data is 111122222221113333333444111223333. The issue I am facing is this, the formula only returns the first instance of 1 and ignores the other instances. Are you able to help me rewrite the formula so that it will return the first of every group of duplicates instead of only the first of all duplicates?

              Meaning, in the scenario where the data is 111122222221113333333444111223333, I need to return the following in a column 1 [blank] [blank] [blank] 2 [blank] [blank] [blank] [blank] [blank] [blank] 1 [blank] [blank] 3 [blank] [blank] [blank] [blank] [blank] [blank] 4 [blank] [blank] 1 [blank] [blank] 2 [blank] 3

              Let me know if I should post this in the other article you referenced earlier. Thanks in advance.

              1. This doesn't return the results I expect. If column A has 111122222221113333333444111223333, I need the first value of every group of duplicates in the corresponding row in column B. Hence, B1=1, B5=2, B12=1, B15=3, B22=4, B25=1, B28=2, B30=3.

                I've tried several formulas combinations, but cannot get that output in Column B.

              2. Hi!
                To extract character from the middle of text, use the MID function.

                =MID($A$1,ROW(),1). ---- B1
                =IF(MID($A$1,ROW()-1,1)=MID($A$1,ROW(),1),"",MID($A$1,ROW(),1)) ----B2

                Copy this formula down along the column.

  7. Is there a way of using the unique formula to keep rows of data based off of the unique value of one column.

  8. Thanks for posting this! The MS documentation for Unique did not include all of the other arguments, so I was struggling to figure how to get unique vals across a column.

  9. Hi, I have a table of 6 columns and 100 rows of which I wanted to return the whole table with unique values of only the 2nd column.

  10. How do I get Unique values in Column C while looking at previous row outputs - seems like i need to use filter but not having any luck
    A B C
    R1 John Mary Output = John
    R2 John James Output = James - John is already an output in R1 above.
    R3 Mary John Output = Mary - John is already an OP in R1 above

    1. Hi!
      I have already replied to you earlier. Excel cannot search for unique values in two ranges at once. Use VBA.

  11. Hi! My question is the same as the "Phillip says: January 24, 2021 at 10:04 pm" question about adding a blank cell after every unique value. Someone wrote a formula that works for Google Sheet but it doesn't work(convert) in Office365 Excel.

    https: //support.google.com/docs/thread/110891774/add-1-empty-cell-inbetween-unique-function-result?hl=en

    Could you please provide an Excel version of the Google sheet formula '=Transpose(ArrayFormula(FLATTEN({Sort(UNIQUE({A3:A,IF(A3:A="","","")}))})))'

    Example
    A1 = unique value
    B1 = blank cell
    C1 = unique value
    D1 = blank cell
    E1 = unique value
    F1 = blank cell
    … and so on.

    It would be a bonus, if there is a way the cells could be developed so I could merge two cells, but I understand that, that might a UNIQUE function limitation.

    Example
    Merged cell A1:B1 = 2022
    A2 = Sales Dollars
    B2 = Product Count

  12. Can anyone explain to me what I am doing wrong? I have a table with information and I want to get the unique values from that table using the formula in the explanation above. I however do not get the unique data in 2 columns but only the first row of data from my table in 1 column right under each other finishing with a #N/A. As you will understand the table has more than 1 row ;-) in fact 1889 rows.
    The formula I used is: =UNIQUE(CHOOSE({1;2};tblEq_lst[PO_nr];tblEq_lst[Eq_nr]))
    I am confused here because in my humble opinion I used the right syntax and do not get the result everyone probably gets reading the comments above.

    1. Hi!
      I can't check the formula that contains unique references to your workbook worksheets, sorry.
      The UNIQUE function can search for unique values in only one column. For finding unique values in a range, see this comment. Perhaps this will help.

      1. Alexander, already figured it out by downloading a workable example that I found on the internet and guess what. The separator I use in formulas is not ',' but a ';' and a comma used in the formula to mark the columns I want to see from the unique function is not to be marked with ';' but with '\' and that does the trick to see the 2 non adjacent columns.

      2. Thanks Alexander, but to be honest I was using the same as explained above by the author under "Find unique values in specific columns".
        She wrote the following =UNIQUE(CHOOSE({1,2}, C2:C10, A2:A10))
        I am using a column name in a table and this column is PO_nr and the other column is Eq_nr.
        Basically these column are similar to the C2:C10 and A2:A10.
        The result shown above by the author does not pop-up when I use the same syntax.
        So, if it is true what you replied it basically undermines the explanation of the author ;-) because you say that a unique reference cannot be for 2 columns. There are also other videos on Youtube (Leila Gharani that also explain the solution described by Svetlana (author).
        Somehow I must be doing something wrong because normally the explanation by Leila works fine.
        Anyhow, thanks for the help Alexander.

  13. Hello, thank you for this great and useful information. But, I want to figure it out if this function works perfectly for my task or not. So the problem is I have duplicate data and I want to delete all duplicate rows EXCEPT the first three rows. Here is the example for more details :

    BEFORE : AFTER :
    Col. A | Col. B | Col. C | Col. A | Col. B | Col. C |
    2.78 8.13 8.45 2.78 8.13 8.45
    6.49 8.13 6.75 6.49 8.13 6.75
    3.70 8.13 12.3 3.70 8.13 12.3
    5.14 8.13 18.0 5.14 blank 18.0
    5.14 8.13 10.2 5.14 blank 10.2
    5.14 8.13 18.4 5.14 blank 18.4
    5.14 9.02 1.07 blank 9.02 1.07
    5.14 9.11 1.07 blank 9.11 1.07
    5.14 9.13 1.07 blank 9.13 1.07
    5.14 9.12 1.07 blank 9.12 blank

    For additional information, deleted data needs to stay as a blank cell in the same position (in the exact row-column), so there is no shift cell after deleting the data.

    My questions : Is it possible to use “UNIQUE” function to this kind of problem? If it’s possible, how do I write the syntax? If it’s not possible, which function I could use and also how do I write the syntax? Thank you very much for your answer.

    1. I'm sorry I messed up the example. It should be written like this :

      BEFORE :
      Col. A | Col. B | Col. C |
      2.78 8.13 8.45
      6.49 8.13 6.75
      3.70 8.13 12.3
      5.14 8.13 18.0
      5.14 8.13 10.2
      5.14 8.13 18.4
      5.14 9.02 1.07
      5.14 9.11 1.07
      5.14 9.13 1.07
      5.14 9.12 1.07

      AFTER :
      Col. A | Col. B | Col. C |
      2.78 8.13 8.45
      6.49 8.13 6.75
      3.70 8.13 12.3
      5.14 blank 18.0
      5.14 blank 10.2
      5.14 blank 18.4
      blank 9.02 1.07
      blank 9.11 1.07
      blank 9.13 1.07
      blank 9.12 blank

  14. Hi,
    Is it possible to get unique values from different columns in different sheets into one single column as output?

    I have 'project codes' (numbers) and 'project names' (text) spread in 3 different sheets. I'm looking for unique values from both these columns to be able to extract from these 3 different sheets into two columns (project codes & project names) in another new sheet. Is it possible? Could you please share the syntax.

    Appreciate in advance. Thanks!

      1. Hi,
        But the content in those 3 columns could be same or different. Either case, I need unique values only. Please refer below example.

        Col. A Col. B Col. C
        R1 289476 297659 297659
        R2 222257 257880 257880
        R3 285468 285468 285468
        R4 209676 209676 213431
        R5 267890 298786 235132

        From the above grid, I would need unique numbers from all 3 columns.
        3 unique numbers from Row # 5 which are all unique and 1 unique number from Row # 3 which is unique across all 3 columns. Please help!

        Thanks.

        1. Hi!
          To extract unique values from multiple columns of numbers, you can use the formula

          =LET(tbl,A2:C6,c,COLUMNS(tbl),seq,SEQUENCE(ROWS(tbl)*c,,0), SORT(UNIQUE(INDEX(tbl,INT(seq/c)+1,MOD(seq,c)+1),,TRUE)))

          To find distinct values that occur more than once in the range, use the formula

          =LET(tbl,A2:C6,c,COLUMNS(tbl),seq,SEQUENCE(ROWS(tbl)*c,,0), SORT(UNIQUE(INDEX(tbl,INT(seq/c)+1,MOD(seq,c)+1))))

          This should solve your task.

  15. If a a a b a a there need to pick unique how

  16. i have a list of duplicate invoice numbers, and total amount of each invoice is between (-500,000 to +1million), amounts are in colum B and inv numbers are in col A. i want to apply unique function , but i dont want to show invoice number have total amount from (-100 to +100)
    Inv Amount
    1 46,159.25
    2 -45.00
    2 -55.00
    4 2,000.00
    6 -101.00
    6 101.00
    19 -50
    19 145
    21 1,500.00
    21 23000
    21 6000
    23 1,500.00
    25 -2,425.81

  17. Hi, I am trying to use the unique function but want to filter out Blank cells and cells that have N/A written in them. Is there any way exclude N/A and Blank cells from being pulled?

  18. Hello! I'm trying to have duplicate values from 3 different sheets (same column range) to be returned and listed in another sheet. Can you please help me?

    For example:
    If "ABCD" appears from all these ranges:
    Sheet 1 (A1:A5), Sheet 2 (A1:A5), and Sheet 3 (A1:A5),
    how do I get "ABCD" to be returned in a cell?

  19. Says in a row cells [A1="Text1", A5="Text2", A6="Text3", A10="Text4"] & I HAVE CATEGORY says A11="Category".

    What is the formula to select those Text1, Text2, Text3 & Text4 from Category? And the results or format will be
    Results: Column Cells A2=Text1, A3=Text2, A4=Text3 & A5=Text4.

    Hoping for your kind help. Thanks

  20. How can i find unique value which is repeated in all documents for example 01 no is occurring with all names what formula can i use so that gives the result 01 as this is repeated with all names.

    Name No
    asif 1
    asif 2
    kamran 3
    kamran 1
    yousaf 4
    yousaf 1
    Junaid 1
    Junaid 6

    1. Hi!
      I am not sure I fully understand what you mean. If the value is repeated in all documents, then it is not unique. The value 01 is not in your data.

  21. Hello,
    I've a list of orders with sold products. Some orders have 2 or 3 items sold. the items sold is spread into 3 or more columns. I want to create a unique list of products sold, and count how many times it was sold. Kindly could you help me with that?
    below is an example

    Column A, Column B, Column C
    item213, item100, item390
    item512, item512,
    item213, item512, item390

    2. I mean i want to end with the following:

      product, how many times it was sold
      item213, 2
      item100, 1
      item 390,2
      item 512, 3

  22. What if the data looks like this and is not sorted in the weird way you have it sorted in this example;

    Asset Management & Custody Banks Asset Management & Custody Banks
    Asset Management & Custody Banks Mortgage REITs
    Auto Components Auto Parts & Equipment
    Auto Components Tires & Rubber
    Automobiles Automobile Manufacturers
    Automobiles Motorcycle Manufacturers
    Banks Diversified Banks
    Banks Regional Banks
    Banks Thrifts & Mortgage Finance
    Beverages Brewers
    Beverages Distillers & Vintners
    Beverages Soft Drinks
    Biotechnology Biotechnology
    Building Products Auto Parts & Equipment
    Building Products Building Products
    Casinos & Gaming Casinos & Gaming

  23. Hi
    Good day!
    how can I check if a certain Project was tag in different category or metric?
    Example:
    Column A Column B Column C
    Project 1 Combine Yes
    Project 1 LOB Yes
    Project 2 Combine Yes
    Project 2 LOB NO
    Project 3 LOB YES
    Project 3 Combined Yes

    how can we determine if Column A should be tag only for COMBINE or LOB and not both LOB & Combine?

    1. Hello!
      Sorry, it's not quite clear what you are trying to achieve. What condition do you want to check?

  24. Hi
    Good Day!
    May I ask for your help to check if in one column we will find if the data were in two category.
    Example:
    Column A Column B Column C
    Alpha

  25. Hi how i can the unique values for a multi response column? Like this:

    Food_water medicine saving
    Saving gas_and_oil
    Food_water
    Medicine saving gas_and_oil
    Saving
    So i want to have this results:
    Food_water
    Saving
    Medicine
    Gas_and_oil

    Thank you so much

    1. Hello!
      You can get the first word from each cell in a separate column.

      =LEFT(A1,SEARCH(" ",A1,1)-1)

      Then find the unique values in that column as described in the article above.

      1. Thank you so much for your reply.
        But what what if there is a choice that they did not choose it the first, so i will not have that choice, as i have a big data more than 15000 row every month ?
        Please give me a solution

  26. Hi! I have a set of data where clients have put in their details. However, some clients have put in details twice or thrice. The Unique formula doesn't seem to work because I am still getting those client's show up in my list more than once. Upon investigating it was found that the reason for this is because they have spelled it in once in all CAPS and in the other entry it all lower case - hence technically it's a unique entry. The one thing that is unique is their email ID...is it possible to return the entire table, but only looking for unique entries in ONE column instead of the entire table? For example my table has data from Column A to S, but I want the unique formula to look for unique columns in Column D ONLY but return Column A to S

  27. I am trying to figure out how to get a count of the unique values produced? How do I do this via formula? I can able to select the data I see a "Count X" at the bottom right hand of the excel window but if I try "=count(unique( ))" the formula doesn't work. Thank you for your help!

  28. hi I am trying to extract unique values from two worksheets within the same workbook into another worksheet which is also in the same workbook. How can I do that?

    example:
    worksheet 1 has:
    aaaa
    bbb
    ccc
    ddd

    Worksheet 2 has:
    aaaa
    ccc
    eee

    The result should be:
    aaaa
    bbb
    ccc
    ddd
    eee

    is this possible via formula? Please let me know thanks

    1. Hello Vinita,
      I wanted to get exactly the above done in one of my projects and I have found an elegant way to get this done. Please see the following implementation of the LET function:
      =LET(
      rows1,ROWS(r.1),
      rows2,ROWS(r.2),
      cols1,COLUMNS(r.1),
      rowindex,SEQUENCE(rows1+rows2),
      colindex,SEQUENCE(1,cols1),
      IF(
      rowindex<=rows1,
      INDEX(r.1,rowindex,colindex),
      INDEX(r.2,rowindex-rows1,colindex))
      )

      In my case I had three columns from three different tables on three different sheets that I needed to combine and get a unique sorted list - all I did was to encapsulate the above LET function first within a standard ENIQUE() function and then the Unique function was encapsulated within a SORT() function. This produced the desired result.

      I hope this post finds you and that you get some use out of the example.

  29. Very useful! But as people above have said, when trying to use this in a Excel Table I get a #SPILL! error. I am trying to make a dynamically updating Table that will add rows as my source data updates. It works fine until I convert the range to a Table. Am I missing something?

    1. Hi Tom,

      Unfortunately, dynamic array functions (and UNIQUE is one of them) do not work from within Excel tables, they can only be used within regular ranges.

      1. Oh that's a shame! Thanks for confirming though, I thought it was just me.

    2. Hello!
      If the formula should write the result to a range of cells, but those cells are not empty, then #SPILL! error

  30. Very helpful, never knew the unique command existed, combining with sort is magic.

    Thank you so much.

  31. Hi, I need a way of being able to count unique strings within a cell in a range and im not entirely sure how. For example if my data set was below, what formula would i use to count the unique instances of the numbers 1-20 for each agenct? The subset of the data for agent A is below the main set below, and i manually count 14 different strings amongst the total data. Those 14 are spread across 6 different instances of Agent A. So i cant count column 1 for agent A, i cant cound column B for uniques... My only thought at this stage is to set up a new table matching 1-20 against Agents A-B, and then doing a manual count for included numbers (count how many times each number shows for each agent), and then doing a sumif >0 on that table.

    Is there a more elegant solution?

    Agent B 1
    Agent B 10, 19
    Agent B 17
    Agent C 17
    Agent A 5, 13
    Agent C 5, 7, 17
    Agent A 1, 5, 11
    Agent B 11
    Agent C 7, 20
    Agent C 11
    Agent A 6, 18
    Agent A 1, 2, 5, 10, 15
    Agent D 1
    Agent B 9
    Agent D 16, 17
    Agent A 9
    Agent A 8, 13, 18
    Agent B 7, 13
    Agent B 8, 12, 16
    Agent B 13, 15, 16
    Agent C 9, 17
    Agent B 2, 5
    Agent B 1, 4, 15
    Agent C 13, 19
    Agent A 5
    Agent C 9
    Agent D 8
    Agent D 4, 9, 12
    Agent C 1, 10, 17, 18
    Agent A 5, 7, 9, 11, 12
    Agent D 7, 12
    Agent C 9
    Agent B 7, 19
    Agent D 1, 7
    Agent D 18, 20
    Agent D 13, 14
    Agent D 3
    Agent B 5, 6, 9, 12

    Agent A 6, 18
    Agent A 1, 2, 5, 10, 15
    Agent A 9
    Agent A 8, 13, 18
    Agent A 5
    Agent A 5, 7, 9, 11, 12

    1. Hello!
      I believe the following formula will help you solve your task:

      =SUMPRODUCT(--(FREQUENCY(FILTER($B$2:$F$45,$A$2:$A$45="Agent A"),FILTER($B$2:$F$45,$A$2:$A$45="Agent A"))>0))

      Each number is written in a separate cell.
      If your numbers are written in one cell as text, then use the recommendations of this article to split the numbers into cells.
      We have a ready-made solution for your task. The Split Text tool is part of Ultimate Suite for Excel. With its help you can separate a column of text into multiple columns or rows. Easily split data by any character, string, or mask.
      You can install it in a trial mode and check how it works for free.

  32. Great information here. Do these formulas allow use of wildcards?
    =IFERROR(UNIQUE(FILTER(A2:B10, C2:C10=F1)),0) The formula works but only for the exact
    F1= abc. need any C column that includes all instances of abc, ex. xyx abc, abc xyz, xyz abc def is needed. I tried using * and + incorporating "*"&F1&"*" but results returned 0.

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =UNIQUE(FILTER(A2:B10,IFERROR(SEARCH(F1,C2:C10,1),FALSE)))

      You can learn more about SEARCH function in Excel in this article on our blog.

      1. Hello each of these filter formulas work alone but not by combining with and *:
        =ROWS(UNIQUE(FILTER($C$2:$C$882,IFERROR(SEARCH("*"&G883&"*",$G$2:$G$882),FALSE))))
        =ROWS(UNIQUE(FILTER($C$2:$C$882,IFERROR(SEARCH("*"&D883&"*",$D$2:$D$882),FALSE))))
        does not work:(too few arguments) =ROWS(UNIQUE(FILTER($C$2:$G$882,(IFERROR(SEARCH("*"&G883&"*",$G$2:$G$882)*($D$2:$D$882,IFERROR(SEARCH("*"&D883&"*")),FALSE))))

        1. Hello!
          Sorry, it's not quite clear what you are trying to achieve.
          What do you want to calculate exactly? Your question is not entirely clear, please specify.

          1. I want to use the two formulas combined as one. The section above titled "Filter unique values based on multiple criteria" mentions the basic formula =UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<G2)))
            Intend to count unique rows using filters for instances that contain "*g883*"(G1) and(*) "*d883*"(G2).
            column d is a smaller subset of g.
            both formulas start the same: ROWS(UNIQUE(FILTER($C$2:$C$882,,,,
            first formula includes : (IFERROR(SEARCH("*"&G883&"*",$G$2:$G$882) which works.(G1)
            second formula includes: (IFERROR(SEARCH("*"&D883&"*",$D$2:$D$882) which works(G2)
            however, combining with * results in error: you entered too few arguments for this function. Thank you for your response and effort to find a solution.

            1. Hi!
              You are using too many arguments in the FILTER function. I am assuming that you want to apply a filter to two ranges and combine them. It is impossible to do this with one function.
              I do not see your data and your explanations are not very clear. I do not have enough information to offer you a solution.

      2. Thank you for the rapid reply and article link for the Find and Search functions and examples for Excel 2019 and 365,

  33. Is it possible to extract unique values in a column and list the values across a set of row. i.e transpose the unique values?

      1. Perfect, thank you.

  34. I have a list of clients in columns for each month. (See below)

    January ----- February ------ March
    John ---------- Fred ------------- Mary
    Fred ---------- Joe --------------- John

    Is there a way to pull a unique list of clients in one row?
    John
    Fred
    Mary
    Joe

    1. Hello!
      Assuming your values ​​are in the range A2:C9, enter the following formula in cell E2:

      =INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

      In this formula, A2:C9 indicates the range of cells from which you want to get unique values. E1 is the first cell in the column where you want to place the result. $2:$9 means rows containing the data you want to use. $A:$C points to the columns containing the data you want to use. Please change them to your own.
      This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter. After that you can copy this formula down along the column to extract unique values ​​until blank cells appear.

      1. Hi Alex, thanks a lot for sharing this great article and the above useful formula for unique values across rows and columns.
        Is there another function that can accomplish the same goal as above that is not using a volatile function (INDIRECT)? The use of volatile functions comes with some limitations.

        Thanks!

        1. Hi!
          To get unique from multiple columns Excel try this array formula in cell E2:

          =IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($D$1:E1,$A$2:$A$20)+($A$2:$A$20=""),0)), INDEX($B$2:$B$20,MATCH(0,COUNTIF($D$1:E1,$B$2:$B$20)+($B$2:$B$20=""),0))), INDEX($C$2:$C$20,MATCH(0,COUNTIF($D$1:E1,$C$2:$C$20)+($C$2:$C$20=""),0))),"")

        2. Oh nevermindd, I found an alternative formula already.

  35. I have external data imported from a csv file and need to present results of UNIQUE or FILTER functions performed on that data in an Excel table. However it returns #SPILL error in the table. Is there a way aorund it?

    1. Hello!
      The UNUQUE function displays the results in the current column. To do this, she needs a number of empty cells at the bottom. The #SPILL error means that there are not enough empty cells to display unique values. Delete the values from the cells at the bottom, or select a different column for the formula.

  36. What if I have a table with 4 columns. Columns 1 and 3 shows names and columns 2 and 4 have values. The names in columns 1 and 3 are similar. I just want to add the values on columns 2 and 4 that correspond to the similar name in columns 1 and 3

    1. Hi,
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  37. Is it possible to use the "Unique" formula getting unique values with input from several columns, even sitting in different sheets. Let's say I have a column with names in column A sheet1 and another set of names in column B in sheet2 and like to combine them and only see unique names?

    1. Hello!
      The UNIQUE function can only select unique values from one range. To select unique values from two ranges, you can use this array formula:

      =IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")

      List1 and List2 - named ranges with data. You can also use regular absolute references.
      The formula is in cell D1. Otherwise change the cell addresses $D$1:D1.
      Press Ctrl + Shift + Enter so that array function to work.
      After that you can copy this formula down along the column.

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Hello,

        Thank you so much for the above! This solves my problem.

        It does pick up 1 blank cell though between List 1 and List 2. Do you know how can I get rid of that please?

        Thank you,

      2. Thanks, it works, but unfortunately this array function needs more processsing than the Unique function so processing time for the number of rows used is too long. Thanks amyway for calrifying the limitations of the UNIQUE function!

  38. First off , thank you for publishing such a comprehensive article regarding the Unique formula.

    I am having an issue where I am using the UNIQUE(FILTER(array, criteria_range = criteria)) and it works fine if there are multiple items in the source list meeting the criteria, however when there is only 1 item in my list that fulfills the criteria , it gets repeated infinitely in my output list. Can you please let me know what am I doing wrong?
    Please see my example below written as UNIQUE(FILTER(Name,Outcome=1):

    Option 1 Option 2
    Name Outcome Name Outcome
    Angela 1 Angela 0
    Andrew 1 Andrew 1
    Mike 0 Mike 0
    Tom 0 Tom 0
    Lisa 0 Lisa 0
    Ben 1 Ben 0

    Result Option 1 Result Option 1
    Angela Andrew
    Andrew Andrew
    Ben Andrew
    Andrew
    Andrew
    Andrew
    Andrew
    Andrew
    Andrew
    Andrew
    Andrew
    Andrew
    Andrew
    Andrew
    .....

    1. My apologies,
      I think I figured out what I was doing wrong. I was dragging the array formula down however this function populates automatically from the first formula cell without having an expression in the next cell. All fixed, thank you.

  39. I have data that is is updated via an external data source. It is a table. I have created several data slicers to make it easier for the users to filter. (5 slicers). I want to include a summary above the data that displays what has been filtered. I tried using the unique function but it pulls all unique values from the whole list and nit just the filtered list. Any thoughts on this? I’ve googled it but have yet to find a solution. Regards Mark

  40. Hi!
    I use excel advanced filter to extract unique records only and copied them at another location. However, this feature does not dynamically update the extracted values if a value is changed in the source column. How can I achieve it? Thanks very much.

  41. If using the UNIQUE formula to return unique values from single column of data, is it possible to have the function leave a blank cell between each value? Such that is returns the unique data results into every other row

  42. Hello,

    I am using this formula to return unique values (text) from 1 column based on a criterion (also text) in another column:

    UNIQUE(FILTER(array, criteria_range = criteria))

    The array column has cells that are either blank or contain "R". In the case when the criterion is met for several entries in the array column (i.e. several blank cells and may 1 or 2 cells with "R"), the formula returns two lines - both "0" and "R".

    How do I remove the "0" and get the formula to ignore blank cells. I have tried the following with no change to the end result:

    UNIQUE(FILTER(array, criteria_range = criteria,""))

    Thank you very much,

    Kirsty

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =UNIQUE(FILTER(A1:A10, (ISNUMBER(SEARCH("r",A1:A10,1))) * (A1:A10<>"")))

      Hope this is what you need.

  43. Hi,

    I'm trying to get all unique values from a list of entries across several columns, is there a way to do this and return in one column?

    For example, if you had the following lists: Customers for product X. Customers For product Y. Customers for product Z.

    And you want one simple list of all unique customers?

    Thanks,

    Matt

    1. I have this same question...
      Did you find a solution?

  44. Hi, thanks for these articles. I'm hoping you can help me with an application of these functions in a different scenario.

    I've got a large data set of people located in different facilities. The names are in one column and the facilities are in another. In a third column is a date on which occurred a specific event. Because these events are recurring, each name and facility may show up many times.

    I used =Unique(Filter( to pull all the unique names from each facility, and it worked great. I was hoping to use a Countif function to see how many times each name is in the list, but I cannot reference the cells with the names that the unique(filter returned. Is there a good solution to count how many times each unique name shows up in the list without having to type the name out in separate Countif functions?

    Example Data Set UNIQUE(FILTER(A:A,B:B="Facility A")) # of Occurrences
    Name 1 Facility A 12/1/2020 Name 1 ???
    Name 2 Facility A 12/1/2020 Name 2 ???
    Name 3 Facility A 12/2/2020 Name 3 ???
    Name 1 Facility A 12/15/2020
    Name 4 Facility B 12/3/2020
    Name 5 Facility B 12/5/2020
    Name 4 Facility B 12/5/2020

    1. Sorry, it pushed all my columns together. Trying again

      Example Data Set ------------------------------------ UNIQUE(FILTER(A:A,B:B="Facility A")) --- # of Occurrences
      Name 1 --- Facility A --- 12/1/2020 ------------- Name 1 --- ???
      Name 2 --- Facility A --- 12/1/2020 ------------- Name 2 --- ???
      Name 3 --- Facility A --- 12/2/2020 ------------- Name 3 --- ???
      Name 1 --- Facility A --- 12/15/2020
      Name 4 --- Facility B --- 12/3/2020
      Name 5 --- Facility B --- 12/5/2020
      Name 4 --- Facility B --- 12/5/2020

  45. Bookmarking this - thanks so much! I've been looking for resources on how to do this for ages - I manage a lot of payment data updated weekly and frequently need to find unique donor IDs within a certain date range.

    Quick question: I'm trying to find a count for only unique IDs that show up for the first time within a given date range. So for example, pulling a list of all unique IDs for 1/1/20-12/7/20, then pulling a list of unique IDs for 12/7/20-12/14/20, and counting/keeping only the IDs that are in the second range and NOT the first. Do you know of any way to do this?

    Thanks!

  46. Hi,

    How can I count the number of items returned by UNIQUE(FILTER when filter returns no results ? In this case, ROWS or COUNTA return 1, when I would like to have 0

    Thnaks
    Olivier

  47. I have used the UNIQUE function and get the same word twice, in this instance the word "Pollard" appears twice. I have tried copying from one cell into all of the cells with this word yet it keeps seeing the same word as two unique entries. Any ideas? Thanks.

  48. How can use unique function in excel 2013

    1. Hello!

      The UNIQUE function does not work in Excel 2013. It is only available in Excel 365.

  49. How do I find unique fields based on 3-4 different criteria? I'm trying to avoid creating multiple pivot tables. I've been researching this and i'm not sure if it is possible.

      1. I'm able to filter if I add 1 additional criteria and it works. If I add another criteria to the equation it errors out and produces calc. This is where the confusion on my part comes from.

        =UNIQUE(FILTER(SFDC!B:B,(SFDC!G:G=Sheet1!B3)*(SFDC!M:M=92)*(SFDC!M:M=91)*(SFDC!M:M=21)))

        1. Hello!
          I don't have your data. Therefore, I cannot determine the cause of the error. Note, however, that the!CALC error occurs when the UNIQUE + FILTER formula finds nothing. Read more in this section above.
          I hope it’ll be helpful.

  50. Hi - great tips, thanks.

    How can you use the UNIQUE function on a table where you keep adding to the columns but dont want to keep updating the formula?

    for example if you had =SUM(1/COUNTIF(F2:F34,F2:F34)) but wanted to keep adding values to the F column without having to change the formula everytime?

    Thanks

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)