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

  1. 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).

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

    1. Hi Andy,

      What formula are you using?

  3. 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.

  4. Hi, about the Extract distinct values ignoring blanks, is there a way to expand this formula (without neeeding to use vba, pivot etc) to include checking for one or more criteria in other columns, before returning the names. I uploaded the workbook. In it, there are 2 resulting tables, each showing distinct names. 'Month' column is for reference only, please ignore it. Non-array formula, if possible, will be best.

    https://drive.google.com/open?id=1l7mGduN0lzaSjLXzkY40H_gzMAC_gqUx

    Thank you!

    1. Hi Eddie,

      To my best knowledge, it can only be done with an array formula, and I have added it to your workbook. However, the Active list returned by the formula is different from your resulting table because a few names are marked as both Active and Non-active.

      1. Hi Svetlana, thanks for your reply. I realized I had previously set the file in gdrive to be read-only. Do you mind sending the updated workbook to my email. Thanks again!

        1. Eddie,

          I've uploaded it to our web-site in case someone else is looking for a similar solution. You can download the workbook here (formula cells are highlighted in blue).

  5. Dear Svetlana,

    What if I want to add a second filter to an array formula? Problem occurs with the expanding part of the formula, let's assume I have the following Data:

    Dimension Name
    0.80 Sur
    0.80 Sur
    0.80 Sur
    0.80 Sur
    0.80 Sur
    0.80 Sur
    0.80 Sur
    1.30x0.70 Sur
    1.60x0.70 Sur
    0.80 Sur
    0.90 Sur
    0.80x0.36 EM4
    0.80x0.36 EMC1
    0.90x0.36 EMC1
    1.00x0.36 EMC1
    0.60 Canto
    0.80 Canto
    1.00x0.75 Canto
    1.20x0.75 Canto
    1.20x0.75 Canto
    1.20x0.75 Canto
    1.00x0.76 Canto

    What I want to obtain is a list of unique values of dimensions per Name, that is to say, the unique dimension values that share a same name. Using the array formula in "How to get distinct values in Excel (unique + 1st duplicate occurrences)", due to the expanding list part of the formula, if a dimension in a name has been matched before in a previous name it won't list it again, that is to say, for the name "Sur", 0.80 will list as a unique value, however for the Name "Canto", 0.80 won't list since it was already listed on "Sur". How could I modify the formula so that it creates a new expanding list each time a new Name appears on the list?

    Cheers and thank you.

    1. Hello,

      If we suppose that your table starts with cell A1, please try to do the following:

      1. Enter the formula below in cell C2:

      =IFERROR(INDEX($A$2:$A$23, MATCH(0, INDEX(COUNTIFS($C$1:C1, $A$2:$A$23,$D$1:D1, B$2:$B$23), 0, 0), 0)), "")

      2. Enter this formula in cell D2:

      =IFERROR(INDEX($B$2:$B$23, MATCH(0, INDEX(COUNTIFS($C$1:C1, $A$2:$A$23,$D$1:D1, B$2:$B$23), 0, 0), 0)), "")

      3. Select the cells where you've entered the formulas and copy them down to the other cells in the columns by dragging the fill handle (a small square at the lower right-hand corner of the selected cell).

      Hope this will help you with your task.

  6. Hi Svetlana,

    Good day. Thank you for the article. It really helped me a lot.

    I just have one problem here in my excel sheet. I get that in your examples, the list exist only on one column. What if my list consist of an array of cells. Please refer to the table below. I have 2 columns of data and I want to make a list of all the data. I hope you could help. Thanks!

    DATA 1 DATA 2 RESULT
    A1 B1 A1
    A2 B1 A2
    A2 B1 A3
    A3 B2 B1
    A3 B2 B2
    A3 B2

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  7. Hi, thank you for your instructions and help!
    I'm wondering if you know of a way to return multiple corresponding values from more than one column (moving on to the next column if a cell is blank) for one Lookup Value?

    1. Hi Mia,

      You can use nested IFERROR functions to do sequential Vlookups as shown in this example.

  8. I have a large spreadsheet. Serial numbers are in Column "A"; Warranty Expiration in Column "E". Serial numbers in Column A may have duplicates.
    For example; Serial Number 12345 may appear in cell A2 and A7.

    Column E may be blank (if no warranty exists); or may have two different dates (12/1/2015 in E2 and 12/1/2019 in E7 for example).

    I need the furthest out warranty expiration date from Column E per unique Serial Number (Col A) - basically consolidate duplicate serial numbers and return a unique warranty end date.

    Ultimately I would see Serial Number 12345 in A2 and 12/1/2019 in E2.

    How do I do that?

    1. Hello, Gina,

      I can suggest you to take a look at the following article about data consolidation in Excel. If we understand your task correctly, it may help you with your task.

      If it won't help, I'm afraid you will need some kind of a macro. Since we don't help with those, please ask around Mr. Excel forum for it.

      I really hope you'll manage to solve the task!

  9. Hi,
    Is there a way out to extract/retrieve every nth value from a filtered data range?

    Thanks in advance :)

    1. Yes.it is possible.
      share some sample data to understand your query properly.

  10. Hello,
    I am trying to create a list of names taken from a column which contains duplicate names within it.
    I want to create a separate table (so not effecting the data itself) which displays the most common values down to the most unique.
    For example:
    David
    Peter
    Paul
    Albert
    Jules
    Hector
    David
    Albert
    Hector
    Hector
    David
    David

    This would then display in a separate table/sheet as:
    David
    Hector
    Albert
    Paul
    Peter
    Jules

    Any help you can give would be invaluable.

    Kind Regards
    Thomas

    1. Hi Thomas,

      You can do it in this way:

      1. In the original table (Sheet1), count the number of name occurrences using this formula.

      2. In Sheet2 (or anywhere you want), extract the distinct names as described in this example.

      3. In Sheet2, replace the formulas that extract names with values using Paste Special (it's necessary because you will need to sort the list later, and Excel has problems with sorting 2 formula-driven columns).

      4. In Sheet2, enter a Vlookup formula to pull the occurrence numbers from the original table.

      5. Sort the distinct list by the occurrences column.

      I've created a small example for your reference and you can download it here. The source data is in Sheet1, the result on Sheet2.

  11. Hi Ms. Svetlana, I need help. I need to be identify data that comes with the wrong code.Please look at the data below
    Account Number AccountType Account Class
    1012929311 01 INDSAV
    1015619275 01 QUACOR
    1015658317 01 QUACOR
    1015680666 01 QUACOR
    1040135492 01 WISACC
    1040148603 02 WISACC
    All account class-WISACC is meant to have an Account Type of 01, how do identify the ones that come with O2 easily

    1. Hello Funmi,

      Is my understanding correct that a text string, say "1040135492 01 WISACC", is input in a single cell? If so, you can highlight wrong entries by creating a conditional formatting rule with this formula:

      =AND(NOT(ISERR(FIND("dress",A2))),ISERR(FIND("01 dress",A2)))

      Where A2 is the top-most cell with data.

      If the data is in 3 different columns, then create a rule with this formula:
      =AND($C2="WISACC", $B2<>1)

      Where B2 is Account Type and C2 is Account Class.

      The step-by-step instructions to create a conditional formatting rule can be found here: How to create a conditional formatting rule with a formula.

      1. HiThere

        Can I extend until 500 - $AF$6:$AF$500. Its looks like when I replace to 500 the formula cannot works'

        =IFERROR(INDEX($AF$6:$AF$16, MATCH(0, COUNTIF($AG$5:AG5,$AF$6:$AF$16) + (COUNTIF($AF$6:$AF$16, $AF$6:$AF$16)1), 0)), "")

  12. Could you please provide the distinct formulas for a list that's in a row? To my disappointment, I wasn't able to understand what you did well enough to adapt it. Thanks for the fine work!

    1. Hello David,

      In fact, the formulas for extracting distinct values from a column and a row are very similar, and you have to adjust just one reference. Please have a look at the following examples.

      Here's the basic distinct formula discussed in this tutorial:

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

      Where:

      - A2:A10 is the source list.
      - B1 is the cell above the first cell of the distinct list (you enter the distinct formula in B2 and then copy it down the column).

      When extracting distinct values from a row, instead of referencing the cell above the first cell of your distinct list, you refer to the cell to the left of the distinct list. For example:

      =IFERROR(INDEX($A$1:$F$1, MATCH(0, INDEX(COUNTIF($A$2:A2, $A$1:$F$1), 0, 0), 0)), "")

      Where:

      - $A$1:$F$1 is the source list.
      - A2 is the cell to the left of the first cell where you enter the formula.

      Extracting distinct values from a row

  13. hi,
    is there any way to get unique data from two column with formula (without helper cell or column or row).
    for example, convert this data:
    a b
    1 mohammad ahooryan

    2 ali mohammadi

    3 mohammad ahooryan

    to :
    a b
    1 mohammad ahooryan

    2 ali mohammadi

      1. Hi,

        My question is related to MOHAMMAD's. If Sheet 1 contains:

        A B
        1 mohammad ahooryan

        2 ali mohammadi

        3 mohammad ahooryan

        is it possible to get the following in Sheet 2:

        A B
        1 mohammad ahooryan

        2 ali mohammadi

        purely by using formulas, such that Sheet 2 automatically updates for distinct values based on the input in Sheet 1?

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)