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.

Interested in learning powerful excel array formula and functions?
Check out the Advanced excel course.

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-excelv4.xlsx
(Excel 2007 Workbook *.xlsx)

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:

Comments (149)