Article updated on February 13, 2018

Introduction

A unique value is a value that only exist once in a list.

Unique list

A unique distinct list contains all cell values but duplicates are merged into one distinct cell value.

Unique distinct list

If your are looking for a unique distinct list array formula, see this blog article:

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Example data and formula

Filter a column and create a new unique list sorted from A to Z

Array Formula in B2:

=INDEX(List, MATCH(SMALL(IF(COUNTIF($A$2:$A$20, $A$2:$A$20)=1, COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""), ROWS(B1:$B$1)), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), 0))

Recommended articles

Extract unique values from a range

Question: How do I extract values only occurring once in a range? Answer: A range (tbl_text) containing text values Array […]

Extract unique values from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

The following article shows you how to extract a unique list from a column:

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

How to enter an array 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:
{=array_formula}

Don't enter these characters yourself, they appear automatically.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Excel 2007 users can remove any #num errors using iferror function:

=IFERROR(INDEX(List, MATCH(SMALL(IF(COUNTIF(List, List)=1, COUNTIF(List, "<"&List), ""), ROWS(B1:$B$1)), COUNTIF(List, "<"&List), 0));"")

copied down as far as necessary.

How to customize the formula to your excel spreadsheet

If your list starts at F3, change B1:$B$1 in the above array formula to $F$2:F2.

Download excel *.xls file

Extract-a-unique-list sorted A to Z from a column-in-excel.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

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

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

ROW(reference)
Returns the rownumber of a reference

SMALL(array,k)
Returns the k-th smallest row number in this data set.

ROWS(array)
Returns the number of rows in a reference or an array