Table of Contents

  1. How to extract unique distinct values from a column
  2. How to extract unique values from a column
  3. How to extract unique distinct values from a column using advanced filter
  4. User Defined function: Extract unique distinct sorted values from a cell range (vba)

Overview

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

Thanks to Eero, who contributed the original array formula!

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, Eero!

How to create an array formula

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

and copy cell B2 down as far as necessary.

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

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

Excel 2007 users can remove errors using iferror() function:

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

and copy it down as far as necessary.

The formula is an array formula. To create an array formula you press Ctrl + Shift + Enter after you have entered the formula.

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))) + CTRL + SHIFT + ENTER

and copy it down as far as needed.

How to handle blank cells in a range

=INDEX(List,MATCH(0,IF(ISBLANK(List),"",COUNTIF($B$1:B1,List)),0)) + CTRL + SHIFT + ENTER

and copy it down as far as needed.

Thanks Sean!

A somewhat shorter array formula:

=INDEX(List,MATCH(0,(List="")+COUNTIF($B$1:B1,List)),0)) + CTRL + SHIFT + ENTER

and copy it down as far as needed.

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.

Download excel sample file for this tutorial.

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

Recommended 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

How to create a unique list

Overview

Unique values are values existing only once in a list.

Example sheet - Filter unique values

Column C filters all unique values values from column A.   This is also created by an array formula. Unique values are values occuring only once in column A.

How to extract a unique distinct list from a column

Array formula in C2:

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

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

and copy cell C2 down as far as needed.

How to customize the formula to your excel spreadsheet

Change the named ranges. If your unique list starts at F3, change $B$1:B1 or C1:$C$1 in the above formula to $F$2:F2

Named ranges

List (A2:A20)
What is named ranges?

Download excel sample file for this tutorial.

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

Read more related articles in the archive.

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

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. Click "Data" in the menu
  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"!

Functions in this article:

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

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

ROW(reference) returns the rownumber 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.

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

copy-of-how-to-extract-a-unique-list-in-excel.xls

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

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

 

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