Unique values in a range are values occuring only once. This is what I am going to do in this blog post using array formula.

Filter unique distinct values in a range creates a list where all values exists but duplicates are merged into one distinct value. See picture below.

See this blog post: Extract a unique distinct list sorted from A-Z from range in excel

Filter unique values from a range

Array formula in B10:

=INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)=1)*(COUNTIF(B$9:$B9, tbl)<>1), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(FALSE, COUNTIF($B$9:B9, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)=1)*(COUNTIF(B$9:$B9, tbl)<>1), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+COUNTIF(tbl, INDEX(tbl, MIN(IF((COUNTIF(tbl, tbl)=1)*(COUNTIF(B$9:$B9, tbl)<>1), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))<>1, 0), 1) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges

tbl (B4:E7)
What is named ranges?

How to implement array formula to your workbook

Change the named range. If your list starts at, for example, F3. Change $B$9:B9 in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.

Extract-a-unique-list-from-a-range using array formula 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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

Related posts:

1. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
2. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
3. Filter values existing in range 1 but not in range 2 using array formula in excel
4. Filter text values existing in range 1 but not in range 2 using array formula in excel
5. Filter common text values in range 1 and in range 2 using array formula in excel
6. Filter a column and create a new unique list sorted from A to Z using array formula in excel
7. Extract unique values from a range using array formula in excel
8. Filter unique rows and sort by date using array formula in excel
9. Filter common values between two ranges using array formula in excel
10. Filter unique distinct text values in a range using “contain” condition in excel