Comments on: How to get a list of unique and distinct values in Excel

If you want to get a list of unique values in a column, this tutorial offers a number of formulas and will teach you how to tweak them for different datasets. You will also learn how to quickly get a distinct list using Excel's Advanced Filter, and how to extract unique rows with Duplicate Remover. Continue reading →

Comments page 2. Total comments: 171

  1. Looking for a formula to fetch results as below
    Source Data:

    Sub roll no marks
    maths 1 8
    Eng 2 9
    maths 3 7
    Eng 4 4
    Eng 1 5
    maths 2 3
    maths 4 7
    Eng 3 6

    Results Needed:

    Roll no Maths Eng
    1 8 5
    2 3 9
    3 7 6
    4 7 4

  2. Hi Am looking for the below formula

    A=1
    B=2 like wise i have so many data.

    My required is AB = 12 and BA=21 how to put formula for this?

    A 1 A 1
    B 2 BG 27
    C 3
    D 4
    E 5
    F 6
    G 7
    H 8
    I 9

  3. Thanks for the quick replay Alexander!

    When trying that array formula including entering with Ctrl + Shift + Enter, it pops up an error message window saying, "That function isn't valid." After hitting "OK" to acknowledge the error, it highlights the word "FILTER" in the formula. Perhaps the syntax is not correct...? Any ideas?

    Thanks for your help to resolve!

  4. Hi,

    How do I modify the formula below if I have another criteria column:

    =IFERROR(INDEX($A$8:$A$8, MATCH(0, INDEX(COUNTIF($C$1:C1, $A$2:$A$8), 0, 0), 0)), "")

    For example:

    A1 B1
    Pete C
    Betty C
    Frank C
    Mary O
    Frank O
    Mary O
    Suzy O

    I want the formula to look only at rows with "O" and return distinct names.

    Result:

    C1
    Mary
    Frank
    Suzy

    Your help is much appreciated! Thank you!

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

      =IFERROR(INDEX(FILTER(A2:A10,B2:B10="O"), MATCH(0, COUNTIF($C$1:C1, FILTER(A2:A10,B2:B10="O")), 0)), "")

      This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.

  5. Great Article-

    I have a follow-up question I cannot figure out. I want to create a list of unique values from another tab that dynamically updates, only I want the new values to always paste/fill in at the bottom of the unique value list. When I change the data source by copying in new data, sometimes new unique values appear in the middle or top of the data set and then these values appear in the middle of my output list. I cannot have this occur as I need the list values to stay in the same rows so that they are matched with some manually input information that goes with them, rather than moving around without the accompanying values in other columns moving with them.

    Please help if there is any possible solution. Thank you!

  6. Hi,
    How to How to get distinct values in Excel (unique + 1st duplicate occurrences), based in a cell reference
    for example
    Column A Column B
    A Apple
    B Banana
    A pineapple
    C Orange
    A Apple

    I want a list of distinct values based in column A i.e if A, then
    Apple
    Pine Apple
    if B
    Banana

    Help Please, Thanks in advance

    1. Hello!
      Study the section in this article, "How to get distinct values in Excel." If you have specific questions after that, I will try to answer them.

  7. Hi
    I am trying to do a lookup from one sheet within multiple rows and columns. Customer, Part number will have different qty breaks with different prices.Rows have duplicate part numbers and customer, possible price.
    Thanks
    How would you recommend the easiest way?

  8. Hi
    I am trying to do a lookup from one sheet within multiple rows and columns.
    Customer, Part number will have different qty breaks with different prices.
    Rows have duplicate part numbers and customer, possible price.
    Thanks

    How would you recommend the easiest way?

  9. I have two sheets
    First sheet has 470 records, some duplicates.
    Second sheet has 1000 records, some duplicates.
    I want to find all unique values on sheet one that are NOT on sheet two.
    So, any value found in sheet two that matches on sheet one will not be shown. I only want unique values from sheet one. If there are duplicates within sheet one that are not on sheet two they should remain after filtering.

  10. Can I get a formula to remove duplicates and blanks when the data is displayed in a row. I tried to make some changes in the formula which is mentioned for data placed in a column, but couldn't get the desired results.

    1. Hello Keshav!
      If the list of values is on the line (for example, A2:P2), then the list of unique values can be obtained using the formula in cell S2.
      Regular unique values formula (completed by pressing Enter):

      =IFERROR(INDEX($A$2:$P$2, MATCH(0,INDEX(COUNTIF($S$1:S1, $A$2:$P$2) + (COUNTIF($A$2:$P$2, $A$2:$P$2)<>1),0,0), 0)), "")

      In cell U2, we write the formula of the array (completed by pressing Ctrl + Shift + Enter):

      =IFERROR(INDEX($A$2:$P$2, MATCH(0, COUNTIF($U$1:U1,$A$2:$P$2) + (COUNTIF($A$2:$P$2, $A$2:$P$2)<>1), 0)), "")

      After that you can copy this formula down along the column.

  11. Regarding the formula +IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), ""), I only getting the first value as it takes B1 and does not update to b2 b3 and so on when I use it. The array is not working.

    1. Hello,
      You write this formula in cell B2.This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter. You have not done so. After that you can copy this formula down along the column.

  12. Hello,
    Great piece of knowledge for a novice like me.
    1. Please address the issue raised by 'adam' and
    2. By applying the formula =IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "") the result is in the same order as was in original column, (a)It should come out sorted alphabetically (b)with other data in the row. Please provide formula for this.
    It will be great help. God Bless You.

  13. =IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "") works but one needs to figure out how many distinct values are going to exist in order to copy and paste the formula that many times. Hence, if there are 10 distinct values then one copies and paste the formula 9 times. Is there a way for one does not need to know how many distinct values are going to exist?

    Thank you

    1. Hello!
      To calculate the number of unique values in a data range, use the formula

      =SUMPRODUCT(COUNTIF(A2:A20,A2:A20)^(2*ISBLANK(A2:A20)-1))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  14. I have a list of employees and each has a unique job category. Based on their job category, I want a unique list of job competencies to show up to select from a pull down list that are only available for that job category.

  15. Hi, Thanks for this wonderful tutorial. I am having one issue with dates. I am trying to find unique dates but for blanks it is showing 00/01/1900 which is not desirable. can you suggest how to remove this.

  16. I have a list of employees with their work dates in a given year. I want to insert which period they fall in: a date from 15th to 14th of following month. So if the date is 01/05/2020 it should fall in the period 12/15/2019-01/14/2020. What formula would do this?
    Employee Work Date Hours PERIOD
    John 01/05/2020 2 12/15/2019-01/14/2020
    John 01/15/2020 2 01/15/2020-02/14/2020
    John 02/05/2020 2 01/15/2020-02/14/2020
    John 03/06/2020 2 02/15/2020-03/14/2020

  17. Hi,
    Is any way to auto up-date next Tab with specified category in ascending order in a row addition/filling data......!
    If in Sheet1, we add a Row with distinct Name 'A" or "B" in column,should auto up-date in next/assigned Tab with same Name "A" or "B" like VLOOKUP but if I apply VLOOKUP, that collect /repeat 1st value.
    Example:-
    Sheet-1
    Team PO No Date Status
    A Akbar 3-Aug-19 Close
    A Babar 3-Aug-19 Close
    A Masood 3-Aug-19 Close
    A Zara 3-Aug-19 Close
    B Mehmood 18-Jan-19 Close
    B Ali 25-May-19 Close
    B Shahzad 21-Sep-19 Close
    B Hina 17-Oct-19 Running

    New Tab
    Team PO No Date Status
    B Mehmood 18-Jan-19 Close
    A Akbar 3-Aug-19 Close
    A Akbar 3-Aug-19 Close
    A Akbar 3-Aug-19 Close
    B Mehmood 18-Jan-19 Close
    B Mehmood 18-Jan-19 Close
    A Akbar 3-Aug-19 Close
    Awaiting helpful response
    Thanks

  18. Is it possible to extra distinct collumned data across a row?
    If so can you please share a simple formula to use?

  19. Is there a way to add a criteria on this formula? I want to execute this formula if it meets a certain criteria from a column? e.g
    Instead:
    =IF(A:A="Criteria",(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1,$A$2:$A$10) + (COUNTIF($A$2:$A$10, $A$2:$A$10)1), 0)), "")

    =IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1,$A$2:$A$10) + (COUNTIF($A$2:$A$10, $A$2:$A$10)1), 0)), "")

  20. How generate list from range in for next column like
    123456789101 to 123456789104
    123456789108 to 123456789111
    The results should be like
    123456789101
    123456789102
    123456789103
    123456789104
    123456789108
    123456789109
    123456789110
    123456789111

  21. Thank You!!

  22. @Svetlana Cheusheva thanks a lot. it worked well for me.

  23. Great formula to extract the unique values of any list. Well done, I would have not been able to figure it out how to do it on my own. Cheers!

  24. hi there
    could you please help me if possible
    in sheet one I have
    in column A the animals Gender
    in column B the animals species
    in column C the animals age
    there are several other column's with weight, colour and other details
    I have sorted my data by column B then by C
    so if there are 15 cats at the top of the sheet followed by 11 dog and then 3 birds and then 14 rabbits
    I would like to take the first 10 rows of each species and copy them into sheet 2
    regards leigh

  25. Hi there,
    Could you please explain that why we are using, and what its meaning that using "zero" value either as match formula's "lookup value", and index formula's "row number" and "column number" ?
    I am referring to this:
    =IFERROR(INDEX($A$2:$A$10, MATCH(0,INDEX(COUNTIF($B$1:B1, $A$2:$A$10)+(COUNTIF($A$2:$A$10, $A$2:$A$10)1),0,0), 0)), "")

    Thank you.

  26. Step 1: Use Google Sheets
    Step 2: =unique(range)
    Step 3: Profit

  27. Hi,
    I want to select unique entries from a range of cells. I tried this formula and it shows "0" as result.
    My range is A3:F12 and i am trying to get the results starting in A15(formula cell). I used the following formula. Please assist.
    =IFERROR(INDEX($A$3:$F$12,MATCH(0,COUNTIF($A$15:A15,$A$3:$F$12)+(COUNTIF($A$15:A15,$A$3:$F$12)1),0)),"")
    Regards,
    Sunny

  28. Column A Column B Desired result
    Class Code Stipend Class Code Amount Count
    J7-288-001 1500 J7-288-001 1500 3
    J7-288-001 1300 J7-288-001 1300 2
    J7-288-001 1500
    J7-288-001 1300
    J7-288-001 1500

  29. Hello!
    Thanks for a great article. I read it with interest and built a list of unique names from the original list of duplicates using the array formula. My issue is that when I add more non-unique entries to the original list, the new list changes order. This is unhelpful as I use this new list in a table that has vlookup references.
    Any ideas why the new list changes the order of the names?
    Thanks!
    Paul

  30. Hi!
    Is there a way to make the locked rows in the 'unique + 1st duplicate' example dependant on another column? That way I am able to do this procedure for multiple unique rows.

    Thank you!

  31. He genius
    Anyone know to extract distinct value between 2 dates and 1 criterion. Criterion is either number or mixed. Example my unique value Column A, Date 1 Column B Date 2 Column & my creteria is column C. How to extract unique value in column A between 2 dates and base on criteria C?

  32. I tried to get the "list of distinct text values ignoring numbers and blanks" to work in a sheet I was working on, to not avail. Then I copied the example and the formulas given and it would not work as shown. No error messages, just nothing in cells. The function result show the first instance in the dialog box, but not the cell, then nothing below the cell.

  33. Thanks alot
    Its so helpful

  34. Hi Svetlana,
    It's a great post; well explained. thanks.

    A question for you:
    Is it possible to use the INDEX formula for an Excel table? I converted your Excel data to a table (Table1) and used the following formula but it does not work:
    =INDEX(Table1[[#Headers],[Product]], SMALL(IF(D$2=Table1[[#Headers],[Seller]],ROW(Table1[[#Headers],[Seller]])-MIN(ROW(Table1[[#Headers],[Seller]]))+1,""), ROW()-2))
    However, VLOOKUP works but as expected it only gives the first occurrence of the matching field:
    =VLOOKUP(D$2, Table1,2,FALSE)
    Any ideas? Many thanks.
    Abbas

  35. Hello,

    Please could anyone help with this?
    I have a large catalogue (30k rows) with partial repetitions and I need to find the unique segments, for example:
    Column A
    Shoes
    Shoes for men
    Shoes for men size 8
    Shoes for men size 10
    Shoes for men size 12
    Shoes for men size 14
    Shoes for women
    Shoes for women size 8
    Shoes for women size 10
    Shoes for women size 12
    Shoes for women size 14
    Jackets
    Jackets ABCAC
    Jackets for men DAXX
    Leather jackets for men REV0
    Jackets for women ABCC
    Jackets for women AABBCCDD

    The unique list I need to find would be:
    Shoes
    Shoes for men
    Shoes for men size
    Jackets
    Jackets for men
    Leather jackets for men
    Jackets for women

    All codes, sizes, etc can be ignored.

    Thank you so much.
    Marcos

  36. Hi
    I've been using this formula to extract values only from columns in my front sheet "FrontPage" but I need to extract values greater than 60. which this doesn't do.
    =IFERROR(INDEX(FrontPage!E:E,SMALL(INDEX(ISNUMBER(FrontPage!E$7:E128)*ROW(FrontPage!E$7:E128),0,),COUNTBLANK(FrontPage!E$7:E128)+ROW(FrontPage!E3))),"")

    I've tried this array formula but it causes circular references due to the row ref
    {=IFERROR(INDEX(FrontPage!F$1:F$2000,SMALL(IF(FrontPage!F$1:F$2000>65,ROW($1:$2000)),ROW())),"")}
    Your help would be much appreciated
    Colin

  37. Is it possible to get the data from the following table in excel:

    Game Player Goals
    ----- ------ ------
    Game1 John 1
    Game1 Paul 0
    Game1 Mark 2
    Game1 Luke 1
    Game2 John 3
    Game2 Paul 1
    Game2 Luke 1
    Game3 John 0
    Game3 Mark 2
    which gives a result like this:

    Game John Paul Mark Luke
    ----- ---- ---- ---- ----
    Game1 1 0 2 1
    Game2 3 1 - 1
    Game3 0 - 2 -

    kindly help with excel formula

  38. Hi Svetlana,

    I am trying to pull data from one column, based off of data from another column, but return both values. I may have missed it because I am relatively novice in terms of Excel but I've been experimenting. Here's an example Data Set:

    Column A Column B

    First 1
    Second 2
    Third
    Fourth
    Fifth 5
    Sixth
    Seventh
    Eighth 8

    I would like it to return:

    Column C Column D

    First 1
    Second 2
    Fifth 5
    Eighth 8

    Is this something that is possible? Would I have to incorporate a Pivot Table or have to code it in Visual Basic? Basically, it's a function that would be able to take a large data set and condense it down. Something where Column A would have titles/values from Line 2 to 100. Columns B through Z would only have 5 or 6 values entered down the column, though. The application is for Wall Panel manufacturing where Column A is the part name (~100 part names) but each Panel only uses 5 or 6 parts; WP1 might use parts 3, 6, 7, 8 but WP2 might use parts 10, 19, 25, 67 so i'd like to condense it down panel by panel.

    Hopefully that makes sense...

  39. how this part is working =COUNTIF($A$2:$A$10, $A$2:$A$10)
    & how this is different from this =COUNTIF($A$2:$A$10,A2)1 since both has same result separately but in formula second option isn't working,please also explain this part more =COUNTIF($J$1:J1, $A$2:$A$10)...??thnx

    1. this one is correct, =COUNTIF($A$2:$A$10,A2) wrongly place 1 at the end.

  40. Nicely done. Thank you!

  41. I need to get the list of duplicates in Column B and it should return its value and the value in Column A.

  42. Hello,
    I have two Worksheets---WS1 Customer column A(match)& Customer Number B(return value) ....WS2 Customer column E ...WS 2 Column Q (formula)- I need a formula that will match WS2 Column E to Column A WS1 and return value of column B WS1

    Thank you in advance!!

    1. To add the columns in WS 1 Customer are not alphabetized. So, therefore other index match formulas I am using, are not working.

  43. Hello Expert
    Is there a way to list distint text value with criteria? Example in the following

    G2:G20 - Emloyee (Text & Number)
    E2:E20 - Work Week (number only)

    is there a way for me employee name in work week 30 to list like below

    WW=30 (Criteria)
    Employee
    CU110
    1267
    NP230
    27786

  44. Your explanations and examples are very impressive. One issue that confused me was how to propagate the array formulas. I found I had to enter the formulas and propagate them before making them arrays. I then had to individually make the formula of each cell an array with CTRL+SHIRt+ENTER.

    Did I miss something in the examples explanations?

    I am also a programmer so I may tend to use VBA when the formulas become too subtle. Maybe your readers may want to consider this as well. The macro recording feature may be another approach as well.

    Best regards.

  45. So I'm trying to utilize the formula function to get it to essentially do it automatically but every time I put in the formula it always kicks back telling me the Cells are empty do I still have to copy and paste in the values? or is there some way that I can have it pull the values from the cells and only display the unique values on its own?

  46. Hi Svetlana,

    I am able to extract unique list as per your instructions. Thanks.

    Could you please help me sort the unique list that is generated?

    1. Hi Ravi,

      You will have to replace formulas with values first (Paste Special > Values), and then you can use the Excel Sort option (Data tab > Sort & Filter group, A-Z button).

  47. this doesn't work, I get banana for every output, why is this?

    1. Hi Andy,

      What formula are you using?

  48. Hello!

    Thank you so much for explaining how to do this. I have two questions.

    Currently, the range I'm using as my source is a named column in a named table in a separate sheet. I've had no problems referring to the source list in the "distinct values ignoring blanks" formula, using the format TableName[Column] in place of each instance of $A$1:$A$20 in the example. However, what I am wanting to do is use this formula in a validation rule on another sheet, so that I can have an automatically populated dropdown list based on TableName[Column]. Of course, simply doing =INDIRECT(TableName[Column]) returns the entire list, which is full of blanks and duplicates, but removing the IFERROR portions of the formula and trying to incorporate INDIRECT(TableName[Column]) does not seem to be working either. Is there anything I can do to modify the formula to achieve my goal?

    If what I described above is not possible, I've thought of an alternative, but that brings me to my second question. If I were to use the "distinct values ignoring blanks" formula as a formula in Sheet 2, define the extracted list of distinct values from TableName[Column] as TableName2[Column1], and then use =INDIRECT(TableName2[Column1]) as a validation rule for Sheet 3, would there be any way to have TableName2[Column1] automatically update/add to itself...? The thing is, I know I will be adding more values to TableName[Column] from Sheet 1 and I'm hoping I can have an up-to-date list of all of its distinct values available to me at any given time. I really have no estimate of just how many values that would be. By any chance, is it possible for the formula to copy itself to the adequate number of cells when a change is detected in the range it depends on, ideally in a way that does not require me to manually update the reference of the validation rule every time? Or something similar to that, maybe? Ah, I hope that makes sense.

    Thank you in advance!

  49. hi,
    How can i modify the above formulas in the article to add a second criteria on the same range that i want to extract ,
    ex: the range is numbers like , 50001, 50002, 50003, 100001,20001,
    i want to extract only the number that start with 5000 but unique with no duplication.

    Thanks in advance.

  50. Hello,

    I have a large list of names from a survey where the names were inputted differently each time. Many issues involved differences of case sensitivity as well as additional spaces. I have used an =UPPER(TRIM(x)) function to remedy these issues, but I cannot reference the resulting cells for the advanced filter function to extract unique names. Is there a way to remedy this problem?

    Additionally some problematic cells contain issues like this:

    Smith JosephJohn
    Smith Joseph John

    Is there a way to identify and fix these cells faster than manually looking at each one?

    Thank you

    1. Mischa:
      Sometimes the condition of the data requires you to do what you can with the help of the software and then there is nothing to do except go through the data line by line and clean it up.

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 :)