Table of Contents

  1. How to extract unique distinct values from a column
  2. Extract unique distinct values (case sensitive)
  3. How to extract unique values from a column
  4. How to extract unique distinct values from a column using advanced filter
  5. User Defined function: Extract unique distinct sorted values from a cell range (vba)
  6. Filter unique distinct values and records from multiple sheets add-in

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!

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

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

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

Array formula in cell B2:

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

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.

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

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

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.

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

Extract a unique distinct list (case sensitive)

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

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

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

or use this regular formula:

=INDEX(List, MATCH(0,INDEX(COUNTIF(C1:$C$1, List)+(COUNTIF(List, List)<>1),0,0), 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"!

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
Dim Value As Variant
Dim temp() As Variant
Dim iRows As Single,
Dim i As Single
ReDim temp(0)
On Error Resume Next
For Each Value In rng
If Len(Value) &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)) = &amp;amp;quot;&amp;amp;quot;
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

Filter unique distinct values from multiple sheets add-in

Filter unique distinct values is an Add-In for Excel 2007/2010 that let´s you extract

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

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

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.
  • All user defined functions can return up to 64000 values or records because of excel vba limits.

What are unique distinct values?

What are unique distinct records?

Examples

How to filter unique distinct values from multiple sheets using the Add-In (click to expand)

Related posts:

Extract a unique distinct list from three columns in excel

Create unique distinct list from column where an adjacent column meets criteria

Extract a unique distinct list sorted from A-Z from range in excel

Unique distinct list sorted based on occurrance in a column in excel

Extract a unique distinct list sorted alphabetically removing blanks from a range in excel