Introduction

A unique list contains cell values that only exist once.

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 extracted from a column

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

Comments(53) Filed in category: Excel, Unique distinct values

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 using array formula in excel

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

Comments(2) Filed in category: Excel, Unique values

Create unique list from two columns

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

Comments(10) Filed in category: Excel, Unique values

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

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

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.

Comments(2) Filed in category: Count values, Excel

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