3 Ways to extract unique values from a range in Excel

This tutorial describes multiple ways to extract a unique or distinct list from a column.

Scenario

Suppose you have a list of customer names. The list has some duplicate values. You wish to extract unique values from it. Unique values would be a distinct list.

Sample File

Click on the link below and download the excel file for reference.

Extract a unique list from a range


Solutions

1. Advanced Filter

Follow the steps shown below in the animation



STEPS
  1. Go to Data tab in the menu
  2. In Sort and Filter box, Click Advanced button 
  3. Choose "Copy to another location"
  4. In "List range :" box, select a range from which unique values need to be extracted (including header)
  5. In "Copy to :" box, select a range in which final output to be put
  6. Check Unique records only
  7. Click Ok

2. INDEX-MATCH (Array Formula)
Extract Unique Values - Formula

FORMULA 

=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"")

Hit CTRL+ SHIFT + ENTER to confirm this formula. If done correctly, Excel will automatically place curly braces {...} around the formula.

After placing curly braces, the above formula would look like this :

{=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"")}

Copy the above formula and paste it into cell D3. And paste it down till the cell D12 (Select the range D3:D12 and press Ctrl+D).

HOW TO USE

Follow the steps shown below in the animation


Version 2 : IF BLANK VALUES IN A LIST

Suppose there are missing or blank values in your list from which you want to extract unique values. In this case, you need to tweak your formula. The modified formula is explained below -

Extract Unique Values given blank values

FORMULA

=IFERROR(INDEX($B$3:$B$15, MATCH(0,IF(ISBLANK($B$3:$B$15),1,COUNTIF($D$2:D2, $B$3:$B$15)), 0)),"")

Copy the above formula and paste it into cell D3. And paste it down till the cell D12 (Ctrl+D).

Hit CTRL+ SHIFT + ENTER  to confirm this formula. If done correctly, Excel will automatically place curly braces {...} around the formula.

After placing curly braces, the above formula would look like this :

{=IFERROR(INDEX($B$3:$B$15, MATCH(0,IF(ISBLANK($B$3:$B$15),1,COUNTIF($D$2:D2, $B$3:$B$15)), 0)),"")}

How this formula works

Step 1 : COUNTIF($D$2:D2, $B$3:$B$15)

Syntax : COUNTIF(range, condition)
It counts the number of cells within a range that meet the given condition
COUNTIF($D$2:D2, $B$3:$B$15) returns 1 if $D$2:D2 is found in $B$3:$B$15 else 0.

For example, for the second distinct record Dave, the formula becomes COUNTIF($D$2:D3, $B$3:$B$15). It is searching values D2 and D3 in the range B3:B15. The array becomes 
={1;1;1;0;0;0;0;0;0;0;0;0;0}. It is 1 when values of D2 and D3 are found and 0 where it is not found.

Step 2 : In this step, we are checking the position of item that has an array value 0 in Step I.

Syntax : MATCH(lookup_value;lookup_array; [match_type] 
It gives the relative position of an item in an array that matches a specified value.

MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0) returns 4 for the second distinct value. It is 4 because the value Dave is placed in the fourth position of the list. [Also see 0 is the fourth value of the step 1 array - {1;1;1;0;0;0;0;0;0;0;0;0;0}]

Step 3 : In this step, we extract the desired distinct value. The INDEX function helps to achieve it.

Syntax : INDEX(array,row_num,[column_num])
The INDEX function returns the reference of cell meeting row and column number in a given range.
INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)) returns Dave.

Tutorial : Excel Array Formula with Examples

3. MACRO (Advanced Filter)

It's an excel macro to pull distinct values from a column in Excel.


VBA CODE



HOW TO USE

1. Press Alt + F11 to open VB editor window
2. Go to Insert menu >> Module
3. Copy and paste the above vba code into the window
Download the workbook

I have added one more way to extract unique values from a range [Updated : June 2016]

4. Remove Duplicates Option 

The most easiest way to extract unique values from a range is to use "Remove Duplicates" option. See the snapshot below -
Unique values : Remove Duplicates Option
Warning : If you want to keep your original data (not overwrite unique values), make a copy of it (Paste original data to another range or tab) Otherwise original data would be removed.
Select range >> Go to Data option >> Click on Remove Duplicates >> Select the column that contains duplicates >> Ok
Important Note :
  1. If you have multiple columns in a range and you want to remove duplicates based on a single column, make sure only the column that contains duplicates is selected.
Remove Duplicates by a column
2. If you want to remove duplicates based on all the columns (whole row), make sure all the columns are selected.

Related Articles
1. Count Unique values in a column
2. Count Unique values in multiple columns
3. Select and Count Duplicate values in Excel

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

49 Responses to "3 Ways to extract unique values from a range in Excel"

  1. It works only for 1 column not for the 2d range

    ReplyDelete
    Replies
    1. Any way to extend this over multiple columns?

      Delete
    2. Concatenate it and then run the concatenated column.

      Delete
  2. You are a legend. Wish you all the best and keep up the good work.

    ReplyDelete
  3. Excellent.

    ReplyDelete
  4. Nicely laid out; especially liked the multiple options to end results. However what I'm looking for is a formula that will seek out the first instance in a column with duplicate values and keep its row place at the same time. In all of your examples, the query column shows a condensed list that does not match across the rows the very first unique instance of that specific value.

    A B
    1 1
    1
    2 2
    2
    1
    1
    3 3
    3
    2
    1
    3

    ReplyDelete
    Replies
    1. Use this formula...

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

      Delete
    2. many many thanks dear...I was looking for this.

      Delete
  5. Hey - it keeps returning "0" value rather than the text in the cells i want. Not sure what i am doing wrong.

    =IFERROR(INDEX('Pocketbook Data'!$C$12:$C$31,MATCH(0,COUNTIF('Chart of Accounts'!$B$4:B17,'Pocketbook Data'!$C$12:$C$31),0)),"")

    ReplyDelete
    Replies
    1. Since it's an array formula, press CTRL SHIFT ENTER to confirm it rather than only ENTER.If done correctly, Excel will automatically place curly braces {...} around the formula.

      Delete
    2. I have the curly brackets and I pressed ctrl+d, but all I have are blank values.

      Delete
    3. Upload your file to cloud drive and share the link with me. Thanks!

      Delete
    4. I had the same issue Matt.
      Check if you have a circular reference on the bottom left status bar.
      I needed to adjust the countif formula to reference the cell above the current cell that you are entering the formula into. This threw me originally as my data started on the first row of the sheet and Deepanshu's example starts on the second row.

      i.e. My first formula was in cell n2 and the example formula references d2 because the first line is blank.

      Further confusing is that when you evaluate the formula using formula auditing it looks to work right up to the last step when it switches from the correct value to zero.

      HTH

      Michael

      Also thanks Deepanshu for taking the time to publish this information.

      Delete
  6. The =IFERROR(INDEX($F$13:$F$1353, MATCH(0,COUNTIF($X$12:X12, $F$13:$F$1353), 0)),"") perfectly worked for me except that I use a filter for the F column. When I deselect any item from the filtered column I would like to have it removed from the unique items list created.

    Is that possible somehow?

    Thanks in advance!

    ReplyDelete
  7. Dim d As Object, c As Variant, i As Long, lr As Long
    Set d = CreateObject("Scripting.Dictionary")
    Sheets("Plan1").Select
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    c = Range("A1:A300" & lr)
    For i = 1 To UBound(c, 1)
    d(c(i, 1)) = 1
    Next i
    Sheets("Plan2").Select
    Range("A1").Resize(d.Count) = Application.Transpose(d.keys)

    ReplyDelete
  8. This formula has helped me tremendously! However, is there a way to make the formula only return a value of a cell if the length of the value is 8 for example?
    I have two spreadsheets, one has a column of data which contains varying lengths of text. On the other spreadsheet I am trying to populate a column with only the values that are a length of 8.
    My goal is to NOT have to manipulate the data on the "source" spreadsheet as there will be others using this workbook and I just want to keep it as a simple copy/paste method for importing the raw data.
    Any help would be appreciated.

    =IFERROR(INDEX(PNRExport!$F$1:$F$800,MATCH(0,IF(ISBLANK(PNRExport!$F$1:$F$800),1,COUNTIF($F$5:F24,PNRExport!$F$1:$F$800)),0)),"")

    ReplyDelete
  9. I used your advice before and find this website extremely helpful, thank you.

    Now I have a situation where a list has blanks and duplicates, very similar to your example “Version 2: IF BLANK VALUES IN A LIST” but here’s the catch . . . for this effort, I have a requirement to keep the duplicates for reporting purposes but remove the blanks. I might be over thinking this.

    Here is the formula I’ve used but of course it removes duplicates which I need to keep:
    {=IFERROR(INDEX(WIP!$A$3:$A$501, MATCH(0,IF(ISBLANK(WIP!$A$3:$A$501),1,COUNTIF($A$2:A55, WIP!$A$3:$A$501)), 0)),"")}

    Do you have a simple way of achieving this?

    ReplyDelete
  10. How can I do this from multiple list ranges from different worksheets, to get a list of unique model numbers, ranges being for example Sheet2!A2:A40, Sheet3!D2:D40, Sheet4!C5:C43, Sheet5!B2:B40. Thanks in advance!

    ReplyDelete
  11. Meant to clarify, it should always update Sheet1 column A with the unique list

    ReplyDelete
  12. Having issues with this query it keeps returning 0 instead of the text. IF(ISERROR(INDEX(Invoiced!$B$1:$C$3101,SMALL(IF((Invoiced!$B$1:$B$3101=$A$5)*(COUNTIF($D$6:D6,Invoiced!$M$1:$M$3101)=0),ROW(Invoiced!$B$1:$B$3101)),ROW(1:1)),2)),"",INDEX(Invoiced!$B$1:$C$3101,SMALL(IF(Invoiced!$B$1:$B$3101=$A$5,ROW(Invoiced!$B$1:$B$3101)),ROW(1:1))*1,2))

    ReplyDelete
  13. Thanks for providing these tips!

    I'm trying do this across 3 columns, but there are no results. If I restrict the range to just one column than it works fine, but nothing for 3 columns.

    Does this not work for multiple columns, and if not, is there any other alternative that does?

    thanks!

    ReplyDelete
  14. Thank you very much! Your website has been SO helpful in my work! This formula is great, however sometimes I need Excel to extract the unique items in alphabetical order. For example, using your illustration above, is there a formula that would return the unique items in the order:

    Dave
    Deepanshu
    Jhonson
    Neha
    Sohan

    ...and not in the order that they occur in the column. I have a similar need with extracting values in ranked order, but have an existing workaround.

    Any advice you could provide would be much appreciated!

    Thanks again.

    ReplyDelete
  15. Another most simple way - button Data/Remove Duplicates.
    Animation for the first solution display it.

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. The vba gives me the first and second as duplicate...
    And the first cell in the list has a name now Its called "Extract"
    Any one has an idea why and how to fix?

    ReplyDelete
  19. Edith Mativenga10 March 2016 at 02:05

    super great

    ReplyDelete
  20. Fantastic Trick to get a dynamic list. I'm a bit confused on how to extend the list beyond 15 rows. I've unsuccessfully changed all values from 15 to 40 rows, but it failed.

    ReplyDelete
    Replies
    1. If you are using advanced filter to extract unique values, you need to give reference of your updated range manually in the 'List Range' box

      Delete
    2. I'd like to use INDEX+MATCH '=IFERROR(INDEX($B$3:$B$15, MATCH(0,IF(ISBLANK($B$3:$B$15),1,COUNTIF($D$2:D2, $B$3:$B$15)), 0)),"")

      Changing references from $B$15 to $b$30 does not pick up addl rows beyond row 15

      Delete
    3. I have changed the reference and it is working. The file is saved here - Working Sheet

      Make sure you are changing reference from $B$15 to $B$30 in all the three places in the formula. Press CTRL SHIFT ENTER to confirm this formula rather than simple ENTER. If you do it correctly, you would see the formula enclosed in curly brackets.

      Delete
    4. Thank you for your quick response and for pointing out my omission to you CTRL SHIFT ENTER - all is well now

      Delete
    5. Glad to know all is well now. Cheers!

      Delete
  21. Hi there,
    It works for me. But honestly I didn't get that formula. I would appreciate if someone helps me to understand.
    Here is my understanding:

    Index($E$2:$E$1000, Match(0,countif($A$3:A3,$E$2:$E$1000),0))
    ---To be specific this is the part I didn't understand---

    countif($A$3:A3,$E$2:$E$1000) --- This function returns 0 or 1 if the value exist in $E$2:$E$1000 range.

    Then comes
    Match(0,countif($A$3:A3,$E$2:$E$1000),0) --- which is generating this value Match(0, 0 or 1, 0) How come Match function doesn't have a range in the middle. In fact its taking countif array as range. Could anyone explain this to me?

    ReplyDelete
  22. Trying to generate column C from data in A and B. Need to take first all same type in "A" and list first and last occurrence of same type in "a". Data is sorted by "A" then by "B".
    A B c
    1 Blue 24"x36" 24"x36' to 42"x66"
    2 Blue 24"x8' 24"x36' to 42"x66"
    3 Blue 42"x66" 24"x36' to 42"x66"
    4 Red 24"x8' 24"x8' to 7'6"x9'6"
    5 Red 7'6"x9'6" 24"x8' to 7'6"x9'6"

    ReplyDelete
    Replies
    1. Sorry, removed my spacing...
      | Col A| Col B | Col C
      1 | Blue | 24"x36" |24"x36" TO 42"x66"
      2 | Blue | 24"x8' |24"x36' to 42"x66"
      3 | Blue | 42"x66" |24"x36' to 42"x66"
      4 | Red |24"x8' |24"x8' to 7'6"x9'6"
      5 | Red |7'6"x9'6"|24"x8' to 7'6"x9'6"

      Delete
    2. Suppose values are placed in range A2:B6. Place the following formula in cell C2 and paste it down till C6.
      =INDEX($B$2:$B$6,MATCH(A2,$A$2:$A$6,0))&"to" &INDEX($B$2:$B$6,MATCH(A2,$A$2:$A$6))

      Delete
  23. How can I extract unique values to a different sheet altogether?

    There's a challenge after applying the above formulas. A window pops up which is titled Update Values :"File Name"


    Any help in this regard will be greatly appreciated. Thanking u.
    Huzefa.

    ReplyDelete
  24. I was racking my brain to come up with a clean solution for this - and your posting was most helpful. Thank you for saving me several hours of frustration! Really appreciated!

    ReplyDelete
  25. In the duplicates list find out unique values stored in array. using vba program......

    ReplyDelete
  26. Thank you for your code which is most usefull, need your kind assistance to modify formula in excel to work with countifs or by using index to return 2 values for example the stage column and user name

    ReplyDelete
  27. Hi Dipanshu ,

    Thanks a lot for posting this - just one thing option 2 gives circular reference error

    ReplyDelete
    Replies
    1. The formula works fine. Save your file to cloud drive and share link with me.

      Delete
  28. Good tutorial but VBA code doesnt work if you have formulas in selected columns which echo text in cells with IFs. The same is with other methods. This tricks work only if you have simple text in every cell or digits result if you use formulas in cells

    ReplyDelete
  29. Sorry it works... my mistake... missing (,) in my code ;)

    ReplyDelete

Next → ← Prev