You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website.

I would choose the advanced filter if you want to quickly create a unique distinct list. It is fast and easy.

If you know that you will be extracting unique distinct values from time to time in the same workbook I would recommend using a formula and an excel defined table. You won't need to repeat the same steps over and over compared to the advanced filter and that will save you time and repetitive work.

However working with a large data set may slow down the formula calculations considerably depending on your computer hardware, so perhaps the User Defined Function [UDF] is a better choice or even better a pivot table, if you have huge amounts of data to work with.

The pivot table is lightning fast even with huge data tables but it does have a little learning curve and it requires a few steps to set it up but in my opinion it is totally worth learning how to use pivot tables.

I have made an add-in that lets you extract unique, unique distinct and duplicate values and records from multiple worksheets. This allows you to easily bring together data from multiple sources in your workbook.

Table of Contents

  1. How to extract unique distinct values from a column [Formula]
    1. How to create an array formula
    2. Explaining formula
    3. Functions in this formula
    4. Download example workbook
  2. Extract unique distinct values (case sensitive) [Formula]
    1. Explaining formula
    2. Functions in this formula
    3. Download example workbook
  3. How to filter unique values from a list [Formula]
    1. Explaining formula
    2. Download workbook
  4. How to extract unique distinct values from a column using advanced filter [Excel feature]
  5. User Defined function: Extract unique distinct sorted values from a cell range (vba) [UDF]
  6. Build a list of unique distinct values [Pivot Table]
  7. Filter unique distinct values and records from multiple sheets add-in
  8. Useful tips
    1. Named ranges
    2. Excel defined tables
    3. Remove errors, Excel version 2007 and later
    4. Remove errors, Excel version 2003 and earlier
    5. Blank cells in range
  9. Useful links

Create a list of unique distinct values

Unique distinct values are all cell values but duplicate values are removed.

Example sheet - How to remove duplicate values

Column A contains names, some cells have duplicate values.  An array formula in column B extracts an unique distinct list from column A.

Array formula in cell B2:

=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

Thanks to Eero, who contributed the original array formula!

or use this regular formula:

Formula in cell B2:

=INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0,0),0))

This post shows you how to: Filter unique distinct row records

Back to top

How to create an array formula

You don't need to follow these steps if you chose the regular formula.

  1. Copy the aray formula above (Ctrl + c)
  2. Double click cell B2
  3. Paste (Ctrl + v)
  4. Press and hold Ctrl + Shift simultaneously
  5. Press Enter
  6. Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:
{=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))}

Don't enter these characters yourself, they appear automatically.

Copy cell B2 and paste to cells below as far as needed.

Back to top

How the array formula in cell B2 works

Step 1 - Create an array with the same size as the list

=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNTIF($B$1:B1,List) returns an array containing either 1 or 0 based on if $B$1:B1 is found somewhere in the array List .

COUNTIF($B$1:B1,List)

becomes

COUNTIF("Unique distinct list",{Federer,Roger; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas} )

and returns:

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

This means the cell value in $B$1:B1 can´t be found in any of the cells in the named range List. If it had been found, somewhere in the array the number 1 would exist.

Step 2 - Return the position  of an item that matches 0 (zero)

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

MATCH(0,COUNTIF($B$1:B1,List),0)

becomes

MATCH(0,{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0)

and returns 1.

Step 3 - Return a cell value

INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

=INDEX(List,1)

becomes

=INDEX({Federer,Roger; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas}, 1)

and returns "Federer, Roger"

Relative and absolute cell references

When you copy the array formula down the countif formula range ($B$1:B1) expands. This is created by using relative and absolute references.

The first cell, B2: COUNTIF($B$1:B1,List)

Second cell, B3: COUNTIF($B$1:B2,List)

and so on.

Care to learn more about constructing powerful array formulas? Check out the Advanced Excel Course, you will also learn Visual Basic of Applications.

Back to top

Excel 2007 users can remove errors using IFERROR() function

When the formula runs out of values it returns #N/A errors (Not Available), you can use the IFERROR function to remove the error and return blank in those cell. Unfortunately it comes with a big caveat, it also removes other formula errors as well. So use this with great caution, if your source table has errors you won't detect it because the IFERROR function returns a blank cell instead.

=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"")

and copy it down as far as necessary.

The formula is an array formula, how to enter an array formula.

Back to top

Excel 2003 users can remove errors using isna() function:

=IF(ISNA(INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))), "", INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0)))

and copy it down as far as needed.

This formula is an array formula, how to enter an array formula.

Back to top

How to handle blank cells in a range

Harlan Grove created a formula to count unique distinct values from a list with blanks. I used the same technique here to filter unique distinct values:

Array formula in cell B2:

INDEX(List, MATCH(0, COUNTIF($B$1:B1, List&""), 0))

Back to top

Functions in the formulas above

MATCH(lookup_value,lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

ROW(reference) returns the row number of a reference

SUM(number1,[number2],)
Adds all the numbers in a range of cells

LARGE(array,k)
Returns the k-th largest row number in this data set.

Back to top

Download excel sample file for this tutorial

Extract-a-unique-distinct-list-in-excelv4.xlsx
(Excel 2007 Workbook *.xlsx)

Back to top

Extract a unique distinct list (case sensitive)

The following array formula lists unique distinct values from a list and it is case sensitive. Aa is not equal to AA.

Array formula in cell C2:

=IFERROR(INDEX($A$1:$A$9, MATCH(0, FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))), 0)), "")

How to enter an array formula

Back to top

Explaining the array formula in cell C3

Step 1 - Transpose previous values

TRANSPOSE($C$1:C2)

becomes

TRANSPOSE({"Unique distinct list (case sensitive)";"Aa"})

and returns

{"Unique distinct list (case sensitive)","Aa"}

Note that the ; changes to a ,

Step 2 - Check if two text strings are exactly the same, also case sensitive 

EXACT($A$1:$A$9, TRANSPOSE($C$1:C1))

becomes

EXACT($A$1:$A$9, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

becomes

EXACT($A$1:$A$9, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

becomes

EXACT({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"}, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

and returns

{FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}

Step 3 - Return relative position in array if TRUE

IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))

becomes

IF({FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}, MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)))

becomes

IF({FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}, {1;2;3;4;5;6;7;8;9})

and returns

{FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE}

Step 4 - Calculate how often values occur in an array

FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)))

becomes

FREQUENCY({FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE},MATCH(ROW($A$1:$A$9),ROW($A$1:$A$9)))

becomes

FREQUENCY({FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE},{1;2;3;4;5;6;7;8;9})

and returns

{1;0;0;0;0;0;0;1;0;0} Aa is found in position 1 and 8 in cell range A1:A8

Step 5 - Find first empty value (0) in array

MATCH(0, FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))), 0)

becomes

MATCH(0, {1;0;0;0;0;0;0;1;0;0}, 0)

and returns 2.

Step 6 - Return value from position 2

INDEX($A$1:$A$9, MATCH(0, FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))), 0))

becomes

INDEX($A$1:$A$9, 2)

and returns "CC" in cell C3.

Back to top

Download excel *.xlsx file
Extract-a-unique-distinct-list-in-excel4.xlsx

Back to top

How to filter unique values from a list

Unique values are values existing only once in a list. Example, AA occurs twice in the list below and is not unique. BB and CC occur only once each and is unique in the list.

Example sheet - Filter unique values

Column C filters all unique values values from column A. Unique values occurs only once in column A.

Example, Roger, Federer is not in column C because there are more than one value in column A. In other words, the name is not unique in column A. You can find the name twice in the list, in cell A2 and A8.

Array formula in C2:

=INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1), 0))

How to enter an array formula

or use this regular formula:

=INDEX($A$2:$A$20, MATCH(0,INDEX(COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1),0,0), 0))

and copy cell C2 down as far as needed.

Back to top

Explaining array formula in cell C2

Step 1 - Count each value in array and check if it is not equal to one

(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1

becomes

{2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}<>1

and returns

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}

This array tells excel that the first value in the array is not unique and that is true because Roger, Federer is not unique in the list. However the second value is FALSE and that value is unique, etc.

Step 2 - Keep track of previous values

C1:$C$1 is a dynamic cell reference, it changes as the formula is copied to cells below. You can read more about absolute and relative cell references here.

COUNTIF(C1:$C$1, $A$2:$A$20)

becomes

COUNTIF("Unique list", {"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Federer, Roger "; "Davydenko, Nikolay "; "Verdasco, Fernando "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Blake, James "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "Almagro, Nicolas "})

and returns

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

A zero (0) means that no values have yet been displayed and that is true in cell C2. However when excel calculates the value in cell C3, cell C2 shows "Djokovic, Novak" and the array becomes {0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}. The second value in the array contains 1. This tells excel that value has already been shown.

Step 3 - Add arrays

COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1

becomes

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE} + {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

and returns

{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}

TRUE is 1 and FALSE is zero. So True + 0 equals 1 and False + 1 equals 1.

Step 4 - Find first zero value in array

A zero in the array indicates {1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0} that the corresponding value is unique and has not yet been displayed in the list.

MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1), 0)

becomes

MATCH(0, {1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}, 0)

and returns 2.

Step 5 - Return corresponding value

INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1), 0))

becomes

INDEX($A$2:$A$20, 2)

and returns "Djokovic, Novak" in cell C2.

Back to top

Download excel sample file for this tutorial.

Extract-a-unique-distinct-list-in-excel.xls
(Excel 97-2003 Workbook *.xls)

To extract duplicates, see this post: Extract a list of duplicates from a column using array formula in excel
Back to top

How to extract unique distinct values from a column using advanced filter

Unique distinct values are all cell values but duplicate values are merged into one distinct value.

  1. Go to tab "Data" on the ribbon
  2. Click "Advanced Filter" button on the ribbon

     
  3. Click "Copy to another location"

     
  4. Click "List range:" and select range to filter unqiue distinct values
  5. Click "Copy to: and select a range
  6. Click "Unique records only"
  7. Click "OK"!

Back to top

User Defined function: Extract unique distinct sorted values from a cell range (vba)

Array formula in cell B2:B8212:

=FilterUniqueSort($A$2:$A$8212)

How to create an array formula

  1. Type B2:B8212 in name box
  2. Type above array formula in formula bar
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys

Back to top

VBA code

I am using the selectionsort function to sort values. You can read more about the function here:

Using a Visual Basic Macro to Sort Arrays in Microsoft Excel

Function FilterUniqueSort(rng As Range)
Dim ucoll As New Collection, Value As Variant, temp() As Variant
Dim iRows As Single, i As Single
ReDim temp(0)
On Error Resume Next
For Each Value In rng
If Len(Value) &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; 0 Then ucoll.Add Value, CStr(Value)
Next Value
On Error GoTo 0
For Each Value In ucoll
temp(UBound(temp)) = Value
ReDim Preserve temp(UBound(temp) + 1)
Next Value
ReDim Preserve temp(UBound(temp) - 1)
iRows = Range(Application.Caller.Address).Rows.Count
SelectionSort temp
For i = UBound(temp) To iRows
ReDim Preserve temp(UBound(temp) + 1)
temp(UBound(temp)) = ""
Next i
FilterUniqueSort = Application.Transpose(temp)
End Function

Back to top

Where to copy vba code?

  1. Press Alt + F11
  2. Insert a module into your workbook
  3. Copy (Ctrl + c) Paste (Ctrl +v) above vba code into the code window

Download example file

Extract unique distinct sorted values from a cell range.xls

Back to top

Filter unique distinct values from multiple sheets add-in

Filter unique distinct values is an add-in for Excel 2007/2010/2013 that lets you extract

  • unique distinct values
  • duplicate values
  • unique distinct records
  • duplicate records

from multiple sheets. The Add-In contains 4 user defined functions.

If a value in one of the ranges changes the function will automatically and instantly update the list.

Features

  • All user defined functions remove blank values and blank records.
  • No error values when all values are extracted.
  • Filter values or records from up to 255 different cell ranges or sheets.

What are unique distinct values?

What are unique distinct records?

Example - How to filter unique distinct values from multiple sheets using the Add-In

Purchase Filter Unique Distinct Values From Multiple Sheets Add-in For Excel 2007/2010/2013 - Price $19 USD

Add to CartView Cart


Questions

Is there a money back guarantee?
Sure, you have un-conditional money back guarantee for 14 days.

Back to top

Useful tips

Excel tables

The Excel defined table is cleverly designed, it is constructed to automatically expand if you add more data. You don't need to do anything.

Here is how to convert a list to an excel defined table:

  1. Select a cell in your list
  2. Go to tab "Insert" on the ribbon and click Table button or press Ctrl + T
  3. Click OK button
  4. Your excel defined table is created

Read more about excel defined tables

Back to top

Named ranges

In excel you can name a cell range, a constant or a formula. You can then use the named range in a formula, making it easier for you to read and understand formulas.

Example

List : A2:A20

Tip! Use dynamic named ranges to automatically adjust cell ranges when new values are added or removed.

How to create a named range

The downside with named ranges is that you need to adjust the range every time you add or delete a value in the list, the named range will then not fit the value list. I recommend using excel defined tables if you know that the list may change in the future.

  1. Select cell range A2:A20
  2. Type List in name box
  3. Press Enter

Array formula and named range in cell B2:

=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))

Back to top

Useful blog posts

Want to learn more about filtering unique distinct values? You must read these blog posts:

Learn more about sorting unique distinct values. Read these blog posts:

Learn more about counting unique distinct values and records. Read these blog posts:

Learn more about filtering and comparing unique distinct records. Read these blog posts:

Read more about custom functions in excel