Introduction

A unique list contains cell values that only exist once.

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

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 in excel

How to filter a column and create a new unique list sorted from A to Z using array formula in excel

Array Formula in B2:

=INDEX(List, MATCH(SMALL(IF(COUNTIF(List, List)=1, COUNTIF(List, "<"&List), ""), ROWS(B1:$B$1)), COUNTIF(List, "<"&List), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

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));"") + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges

List (A2:A20)
What is named ranges?

How to customize the formula to your excel spreadsheet

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

Download excel example 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

Related posts:

1. Create unique distinct list sorted based on text length using array formula in excel
2. Unique distinct list from a column sorted A to Z using array formula in excel
3. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
4. Create a unique distinct alphabetically sorted list, extracted from a column in excel
5. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
6. Filter unique rows and sort by date using array formula in excel
7. Filter unique values from a range using array formula in excel
8. Unique distinct list sorted based on occurrance in a column in excel
9. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
10. Filter duplicate rows and sort by date using array formula in excel