Excel Extract a Unique List

41 thoughts on “Excel Extract a Unique List”

1. I have a column with 30k rows of IDs with a total of 45 unique IDs. I would like to add this formula so everytime I copy and past data on the IDs column, the formula will show me just the unique IDs on a separate column.

   IDs Data is in B5:B30005 (B5 is the header)
   In D6 I m inputting the following formula:

   =IFERROR(INDEX($B$6:$B$30005,MATCH(0,COUNTIF($D$5:D5,$B$6:$B$30005),0)),””)

   And it is not working as it should

   Reply

   • Hi Dave,

     Are you entering the formula with CTRL+SHIFT+ENTER as opposed to just ENTER?

     Mynda

     Reply

2. what if you want to filter by the largest to greatest instead of a~z

   Reply

   • Hi,

     I guess you mean sorting rather than filtering? And I’m not sure what you mean by ‘largest to greatest’? Isn’t that the same thing? You haven’t given any context, so I’m going to assume you have Dynamic Arrays and you are sorting numbers in A1:A10.

     Sort in ascending order

     =SORT(A1:A10)

     Sort in descending order

     =SORT(A1:A10,,-1)

     Regards

     Phil

     Reply

     • {=IFERROR(INDEX(SORT, MATCH(LARGE(IF(COUNTIF($B$20:B20, SORT)=0, COUNTIF(_2019, “<"&_2019), ""), 1), COUNTIF(_2019, "<"&_2019), 0)),"")}

       I wrote this formula but it seems to not go thru the whole list of names… I have close to 600 customer but it only sorts to lik 90 and the rest just list of a single customer name… I don't really know what the formula is doing but… i feel like i missing something in the formula…

       Reply

       • Hi,

         Please start a topic on our forum and attach your workbook. It’s too easy to make mistakes if we have to recreate the data you are working with.

         Regards

         Phil

         Reply

         • how do i do that?

           PLS also note that the data I’m working with is very huge

         • From the menu at the top of the site. Forum -> Register as Forum Member.

           Then read the Forum Rules and Guides to see how to post a question and attach files.

           Regards

           Phil

3. =IFERROR(INDEX($B$6:$B$10,MATCH(0,COUNTIF($D$5:D5,$B$6:$B$10),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?

   Reply

   • Hi Cal,

     The formula already has error handling, so you can simply drag it down further than you need, allowing for growth in the list.

     Mynda

     Reply

4. Hi I have an sheet with three column First Colum having some drug names, second having manufactured date, third having expiry date. I want to get list of drug names which are manufactured and not expired on the date entered a cell of another sheet.Or Simply want to filture out few list of items on some conditions on another cell

   Reply

   • Hi Jaigopal,

     This can be done quite easily with Advanced Filter or adding a helper column and using the filter drop downs on a Table. If you post your question and sample Excel file on our forum we can give you a specific answer with an example.

     Mynda

     Reply

5. In my experience, the simplest formula would be a vlookup with fixed starting point and moving end. If this returns an error, then the value is unique. So this must be used in conjunction with If(iserror(),TRUE,FALSE) so as to capture a TRUE and FALSE value. FALSE =””.

   Reply

   • Hi Rolan,

     I’m not seeing how that would work. But now that we have Dynamic Arrays the simplest formula uses the UNIQUE function e.g. =UNIQUE(range)

     Mynda

     Reply

6. Hello Mynda,

   My non-array formula for the sorted distinct list starting in Cell N6, copied down to Cell N10, is this:

   =IFERROR(LOOKUP(2,1/(COUNTIF(M$6:M$10,”>=”&M$6:M$10)=MAX(INDEX(
   COUNTIF(M$6:M$10,”>=”&M$6:M$10)*(COUNTIF(N$5:N5,M$6:M$10)=0),0))),
   M$6:M$10),””)

   Reply

   • Thanks for sharing, Robert!

     Reply

7. Regarding: =IFERROR(INDEX($B$6:$B$10,MATCH(0,COUNTIF($D$5:D5,$B$6:$B$10),0)),””)

   How can I extend this to cover two columns of source data?

   Reply

   • Hi Mark,
     instead of returning a null string at the iferror argument, use the same formula with the second column:
     =IFERROR(IFERROR(INDEX($B$6:$B$10, MATCH(0,COUNTIF($D$5:D5,$B$6:$B$10),0)), INDEX($C$6:$C$10, MATCH(0,COUNTIF($D$5:D5,$B$6:$B$10),0))),””)

     Reply

     • ok it works. Turns out your “” from the reply were causing the issue. Any idea how to remove/ignore empty cells?

       Reply

       • See:
         https://www.myonlinetraininghub.com/excel-remove-blank-cells-from-a-range
         https://www.myonlinetraininghub.com/excel-ignore-blanks-in-data-validation-list
         They should be what you need.
         Regards,
         Catalin

         Reply

         • previous one worked fine. no two last questions –
           1. how to ignore empty cells
           2. how to ignore specific value (text) of the cell?

           1 and 2 are separate functions

         • Hi Mark,
           in case you missed it, I already sent a link to an article describing how to ignore empty/blanks.
           I suggest using power query solution to solve all the problems, including removing duplicates, sorting the final list, ignoring specific values, complex formulas may be difficult to maintain.

8. Very useful tools and formulas…

   Reply

   • Thanks 🙂

     Reply

   • Thanks, Irfan!

     Reply

9. Is there any way to modify the formula in “Excel Formula to Extract a List of Unique Values from a Column” to work with empty cells? In other words I would like a sorted unique list of only non-blank cells.

   Reply

   • Hi PB,
     No need to change this formula, try this article about removing blanks from a range, it will not remove duplicates.

     Reply

10. Wow!! This was amazing!!! I immediately had to share these functions with my coworkers! Thank you Mynda!!

    Reply

    • Thanks Samuel, glad it was useful for you.

      Reply

11. 5. Then highlight the range you’re looking to fill and Fill, Down. (Highlighting the range and F2, CNTL+SHIFT+Enter doesn’t work)

    Reply

    • Hi Keith,

      Where do I mention step 5?

      Mynda

      Reply

12. How would you combine this with criteria from a validation list? Ex: Store A in validation list uses formula to extract a list of unique values from a column (sales reps)?

    Reply

    • Hi John,
      In the downloadable file, there are examples for 6 ways to extract unique values. At least one of them will take additional criteria, Power Query can take any number of criteria you want, but depends on your specific needs.
      If you can’t make any example work, please upload a sample file on our forum to see your structure. (create a new topic)
      Catalin

      Reply

13. Thank you very much Mynda for sharing these valuable insights! I can’t wait to receive your fantastic tips every week.
    I would like to ask you how the COUNTIF function works in the formula.

    Reply

    • Hi Juan,

      The COUNTIF function is counting the number of times a name in the range of cells above the current cell (in column D) is found in the ‘List of Names’ in column B. The MATCH function is looking for names that aren’t already in the distinct list, hence it’s looking for a zero count. When COUNTIF doesn’t find the name it returns zero, which is a ‘MATCH’. The position of that match is given to INDEX so it knows which name to return next. However, if the COUNTIF finds a name in column D that is in the list of names in column B it will return 1, and so it will be ignored.

      Mynda

      Reply

      • Thank you very much Mynda for the reply, now the formula is beginning to make sense for me. It’s very creative to combine these functions to create a formula that allows to achieve this result, great!

        Reply

      • I have another question about the second formula: what’s the meaning of this formula combined with SMALL function: COUNTIF($M$6:$M$10, “<"&$M$6:$M$10)?

        Reply

        • Hi Juan,

          The COUNTIF tells SMALL which is the next name to return. It’s what enables the names to be sorted. If you use the Evaluate Formula tool on the Formula bar you can see how it evaluates. That should help you understand how it’s working.

          Mynda

          Reply

          • Thank you Mynda. I tried to evaluate the formula COUNTIF($M$6:$M$10, “<"&$M$6:$M$10) and the result is {4;1;2;0;2}. I do not understand well what it means. The fourth name of the result is Craig and it's sorted in the first place in the new list, I suppose that's the meaning of the first "4". But the "1" number does not reflect the position of the next name of the range M6:M10 in the new list.

            I am amazed how you achieved to create such a complex formula! That requires a lot of critical thinking 😮

          • Hi Juan,

            You can’t just look at the COUNTIF part of the formula in isolation, you have to read the whole formula to make sense of how it works. Here is the formula in cell O6:

            =IFERROR(
            INDEX($M$6:$M$10, 
            MATCH(
            SMALL(
            IF( COUNTIF($O$5:O5, $M$6:$M$10)=0, 
            COUNTIF($M$6:$M$10, "<"&$M$6:$M$10) , "") 
            ,1), 
            COUNTIF($M$6:$M$10, "<"&$M$6:$M$10) , 0) ) ,"")

            It says index the range M6:M10, match the smallest value that hasn’t already been returned and return it, but if there’s an error, return blank. The first COUNTIF checks if a name has already been returned, if it has been returned i.e. it’s already in the list in column O, then “” is input into the array returned by COUNTIF so that this name can be skipped.

            The second and third COUNTIF formulas return the order of the names. {4;1;2;0;2} with 4 being the last name to return (i.e. Richard), and zero being the first name (i.e. Craig). SMALL looks for the smallest name still present in the array. If the name has already been listed then it will have “” in it’s position so small will pick up the next name.

            Looking at cell O6 it evaluates like so:

            =IFERROR(INDEX($M$6:$M$10, MATCH(SMALL({4;1;2;0;2}, 1),{4;1;2;0;2}, 0)),"")

            =IFERROR(INDEX($M$6:$M$10, MATCH(0,{4;1;2;0;2}, 0)),"")

            =IFERROR(INDEX($M$6:$M$10, 4),"")

            Whereas looking at cell O7 it evaluates like so (notice the 0 for Craig in the first array is now “” because it was found in the first COUNTIF):

            =IFERROR(INDEX($M$6:$M$10, MATCH(SMALL({4;1;2;"";2}, 1), {4;1;2;0;2}, 0)),"")

            =IFERROR(INDEX($M$6:$M$10, MATCH(1, {4;1;2;0;2}, 0)),"")

            =IFERROR(INDEX($M$6:$M$10, 2),"")

            The complexity of this type of formula is exactly why I don’t recommend you use it. There are simpler ways to achieve the same results, so why go down a path that’s unnecessarily complex.

            Mynda

          • Thank you very much Mynda for the explanation. It is still very difficult to understand how it works.
            It would be interesting to develop a course on how to order the ideas to create such nest formulas, strategies to use to combine multiple functions