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 5. Total comments: 171

  1. Hi Svetlana,

    Instead of finding unique names in a column, I need to find unique names in multiple columns. Is there an easy way?

  2. Thank you for the info.
    I would like to find out, for "Extract distinct values in a column ignoring blank cells", how can I sort the list by alphabetical order.

    regards.

    1. Hello, Wendy,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  3. Thank you for the information.
    I would like to know how to "Extract distinct values in a column ignoring blank cells" and also arrange them in alphabetically order.

    Many thanks in advance!

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

  5. Hey -- I tried this on excel 2016 and it isn't working because COUNTIF isn't returning an array - simply one value which doesn't correspond to an index in MATCH. Any ideas on why this is happening in my case?

  6. What is limitation for range?

    500 rows is ok

    1000 rows is NOT OK (shows doubles)

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

  8. I have a 19+ digit variant number always starting with 6................-... The "-" is always on number 19 slot after which a digit or two or three or four digits follows. These unique numbers are always exist once within column H to X in each row of data. Sometimes, i could have 60 thousand row from which to extract this value into one specific column. Definitely, l need formular help to achieve this task. Can you help me please with something that will do this and still give me the result as editable values?

  9. Please help!!!
    I want to extract the list(data validation) from the data as below. First lookup the PO# and then create a list of corresponding distinct values.

    PO# Category
    KIPS/IT/01 CPU
    KIPS/IT/01 LCD
    KIPS/IT/01 LCD
    KIPS/IT/02 Cable
    KIPS/IT/02 CPU
    KIPS/IT/02 LCD
    KIPS/IT/03 Connector
    KIPS/IT/04 CPU
    KIPS/IT/04 LCD
    KIPS/IT/04 Cable
    KIPS/IT/04 Connector
    KIPS/IT/04 Mouse
    KIPS/IT/05 CPU
    KIPS/IT/05 LCD
    KIPS/IT/05 Cable
    KIPS/IT/06 CPU
    KIPS/IT/06 LCD
    KIPS/IT/06 Cable
    KIPS/IT/06 Connector
    KIPS/IT/06 Mouse
    KIPS/IT/07 Webcam
    KIPS/IT/07 Toner
    KIPS/IT/07 Miscellaneous

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

  11. Hey,Thanks for your help...
    I have a query on the same..I have been trying some expansion to this formula but no luck. I need to use this formula with couple of conditions, Such as date range from Jan to Mar or Apr to Jun or Jan to May, something like that..Appreciate your help.. thanks in advance

  12. Value of: Employee=500 & Relative=700

    How do I get respective value 500/700 in B1 when I write Employee/Relative in A1
    Please help.

  13. Dear Svetlana,

    I need to extract a corresponding value from say column i and j for a value from column b, and my 2nd number would be 'previous value' in column 'b' plus constant c and there corresponding value from columns i and j.

    b c d e f g
    6.950323 506.995 506.995 0.235604169 46.54370561 0.211560057
    57.50959672 0.187815384 56.3904946

    b+c
    7.149519 508.124 508.124 0.242356576 46.6473513 0.217010041 57.95264365 0.193082442 56.7702949

    this continues for entire data, with value of 'c' being constant getting added to value from column b, like equally spaced...

    Could you please help me out on this.

    With Best regards
    Rajesh KC

  14. Hello..

    i have data like below.
    sid dose date
    101 a 11-Jan-2017
    101 a 13-Jan-2017
    101 a 9-Jan-2017
    101 b 12-Jan-2017
    102 a 11-Jan-2017
    102 a 1-Jan-2017

    I want data like: I want unique subject, and unique treatment and minimum date in unique treatment.

    Can someone let me know how can u do that..

    Thank you
    Satheesh K

  15. I have a problem. I want to find and count duplicate values in two ranges. Suppose I hv two sheets called A & B, there are both similar & common values in both A:A range in A sheet & A:A range in B sheet. In both separate sheets A & B, if one common value matches/finds/exacts/duplicates then count otherwise dont. Anyone have some suggestions how to do it????

  16. I have a table that contains multiple values in column B for their corresponding dates in column A. For each date, i want to extract the values that appear only once and leave out the values that appear more than once. How do I do this please?

  17. Hi..
    Example
    My unique value is : 120450
    and in other sheet the unique value is with some other text for example: Rishabh 120450

    it is a huge list and in each line there is something other than Rishabh, but the unique value is there everytime with a space.
    I cant use Text to column to remove space as all the cell have different no of space.

    Pls suggest can we use Vlookup to find 120450 in the other sheet?

    Rishabh

  18. Dear Svetlana,
    I have a issue coming up with formula for my unique list of Issues (Issue)column and attached to it sub category (Issue Details). I have managed to come up with Unique list of Issue details but some of it fall for main Cat Issue twice. For example: Issue Detail "procedure breach" may fall in 2 types of Issues "Policy" and "communication". Is there any way that you could direct me to the formula that could deal with such a dependency ? Or give me a hint how this issue could be resolved ?

  19. Hi All,

    I need Unique (Distinct) count of project (only active & approved)
    By Month wise

    Example: Please find below scenario

    Project Status Date
    15746 active 1-Jan-16
    15780 active 20-Jan-16
    15795 active 5-Jan-16
    15822 active 21-Jan-16
    15822 active 22-Jan-16
    15822 Closed 2-Jan-16
    15780 Approve 2-Feb-16
    15822 active 22-Feb-16
    Answer: for Jan month:4 unique count(only active and approved Supplier)
    Can anyone help me out on this!!

    Thanks in advance
    Sri

  20. Hi Svetlana Cheusheva,
    i have a lot of data like this in one column and want to sort it for further working.
    113-00-00-00-0000-10121-5109-00000
    113-00-00-00-0000-10121-5110-00000
    113-00-00-00-0000-10121-5151-00000
    113-00-00-00-0000-10405-1114-00000
    113-00-00-00-0000-10437-1112-00000
    113-00-00-00-0000-10441-1112-00000
    i want to create a formula to select all cells in other sheet which have suppose "10121".
    Please help me to arrange my data.

  21. Thank you , =IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "") did work now for me.

  22. I have data

    001 - Xyz
    001 - ABC
    001 - DEF
    002 - MNO

    I want all the values of 001 in one cell. Please advise

  23. Hi All,

    I have a question. Will i be able to aggregate non numeric data based on one just one of the columns in excel. I want all aggregation at this level. I know this is possible if there are 2 columns through pivot. But i have 22 columns in my sheet. How do i aggregate based on just on of the columns ?

  24. When I try using your formulas (for distinct) I keep getting a ERROR:504 (using Open Office, and I have changed the commas to semi-colons). What am I missing? (Thank you in advance!)

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

  26. Hey,

    I did formula in one cell. After that if i drag down that formula will give incorrect detail. How to solve this? which keys i have to press?

  27. Fantastic...nicely done...thank you!

  28. Hi there,

    First, this is a wonderful site and I appreciate all your effort. I wonder how you can use Excel to:

    I have three columns all with 7 digit numbers. All three have overlapping values. I am trying to only find the values in column A that are not found in Column B or C.

    Find Unique Values in Column A not found in Column B or Column C?

    Any assistance would be greatly appreciated.

    Eric

  29. 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)), "")

  30. for unique value, you can use =IF(COUNTIF(F$2:'F2,F2)=1,F2,"")its working as well

  31. Hi Ms. Svetlana;
    Just need some help.

    In column A and B as shown below:

    Column A Column B

    00123 Revised
    00124 Revised
    00123 Cancelled

    *As you can see "00123" appeared twice but with different
    value on Column B., any formula that can count the last
    occurrence of the data in Column A with the value on
    Column "B"

    Thanks for the Help.

  32. Hello,

    I'm trying to get my sheet to track unique and duplicates on "sheet2", then note how many of each from "sheet1" on "sheet2" next to the unique and 1st duplicate.
    the biggest issue I'm having is my "names" are all numerical values.

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

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



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