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. Download example workbook
  2. Extract unique distinct values (case sensitive) [Formula]
    1. Explaining formula
    2. 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
  10. Functions in all above formulas

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$20,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$20),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($A$2:$A$20,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$20),0))

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

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

COUNTIF($B$1:B1,$A$2:$A$20)

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 cell range $A$2:$A$20. 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,$A$2:$A$20),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,$A$2:$A$20)

Second cell, B3: COUNTIF($B$1:B2,$A$2:$A$20)

and so on.

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($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))), "", INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 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($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20&""), 0))

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

Unique distinct formulas

Filter unique distinct row records
Extract a list of unique distinct records and other related examples.
Unique distinct list from two columns
Learn how to build a formula that merges two columns to a unique distinct list
Unique distinct list from three columns
This post demonstrates a formula that merges three columns to a unique distinct list
Unique distinct list from multiple columns
How to merges multiple columns to a unique distinct list
Unique distinct list using a condition
Learn how to build a formula that creates a unique distinct list using a condition. The ISTEXT function identifies text values in an adjacent column.
Create a unique distinct list using two conditions
Construct a formula that creates a unique distinct list using two conditions. Find numbers in an interval and extract unique distinct values from an adjacent column.

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

Back to top

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

Back to top

Case sensitive formulas

Filter unique distinct records (case sensitive) [UDF]
A User Defined Function to extract unique distinct records (case sensitive).
Count unique distinct values (case sensitive)
This post demonstrates a formula on how to count unique distinct values (case sensitive)in a cell range
Count multiple text strings in a cell range (case sensitive)
This post demonstrates a formula on how to count text strings (case sensitive) in a cell range

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

Lean more about other powerful excel features

Advanced filter is not the only powerful built-in feature in excel, here are some more:

Excel Pivot table
This post shows you how pivot tables work. Pivot tables are perhaps the most powerful feature in excel and also the least known.
Excel defined tables
Excel defined tables lets you organize, filter and manipulate data with ease.

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

More powerful UDFs

Filter unique distinct records (case sensitive) [UDF]
A User Defined Function to extract unique distinct records (case sensitive).
Lookup and return multiple values concatenated into one cell [UDF and formula]
Use a condition to extract values and concatenate them.
Filter unique distinct words from a cell range [udf]
Extract only unique distinct words from a cell range

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

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

Structured references are cell references to an excel defined table. They let you easily see what the data contains as long as you give it good descriptive column header names.

I recommend you use excel defined tables instead of named ranges or dynamic named ranges as long as you are working with more than one value.

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

Functions in all above array formulas

INDEX function explained
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
COUNTIF function explained
COUNTIF(range, criteria)
The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify.
MATCH function
MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value in a specific order
How to use the IFERROR function
IFERROR(value, value_if_error)
Returns value_if_error if expression is an error and the value of expression itself otherwise.
IF function explained
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
SUM function explained
Learn how to use Excel SUM function and it's arguments. The SUM function adds all the numbers in a range of cells. Many examples and a workbook for you to download.
How to use FREQUENCY function
FREQUENCY(data_arraybins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than the bins_array
TRANSPOSE function
TRANSPOSE(array)
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range is a range with values in one column, a horizontal range has values in one row. You can also transpose a range with more than one column and one row.
ROW function explained
ROW(reference)
Returns the row number of a reference
How to use SMALL and LARGE function
LARGE(array,k)
Returns the k-th largest number in a data set.

EXACT(text1,[text2],)
Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case sensitive.