Table of Contents

  1. How to extract unique distinct values of a column
  2. How to extract unique values of a column
  3. How to extract unique distinct values of a column using advanced filter


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

How to extract a unique distinct list from a column

Edit 2009-06-29

Formula in cell B2:

=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))+ CTRL + SHIFT + ENTER and copy it down as far as necessary.

Thanks, Eero!

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

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

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(range,criteria)
Counts the number of cells within a range that meet the given condition

In this example the returning array is (0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0). See picture below.

This means the cell contents in $B$1:B1 can´t be found in any of the cells in the named range List.

If it would have been found, somewhere in the array the number 1 would exist.

Then the array formula uses MATCH() to find the first 0 (zero) value in the returning array.

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

0  (zero) is found first in the array. MATCH(0,COUNTIF($B$1:B1,List),0) returns the number 1.

MATCH(0,(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),0) is 1.

=INDEX(List,1) is Federer, Roger

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.

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: COUNTIF($B$1:B1,List)

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

and so on.

See picture below. Click for a larger image.

explaining

How to create a unique list

Unique values are values existing only once in a list.

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)) + CTRL + SHIFT + ENTER and copy the formula 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 of 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

This blog article is one out of thirteen articles on the same subject "unique".

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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
  2. Unique distinct list from a column sorted A to Z using array formula in excel
  3. Unique distinct list sorted based on occurrance in a column in excel
  4. Filter unique distinct values using “contain” condition of a column in excel
  5. Validate unique distinct list in excel
  6. Filter a column and create a new unique list sorted from A to Z using array formula in excel
  7. Create a unique distinct sorted list containing both numbers text removing blanks in excel
  8. Extract unique distinct numbers from closed workbook in excel (formula)
  9. Create unique distinct list sorted based on text length using array formula in excel