Question: How do I extract values only occuring once in a range?

Answer:

A range (tbl_text) containing text values

Array formula in B14:

=INDEX(tbl_text, MIN(IF((COUNTIF($B$13:B13, tbl_text)=0)*(COUNTIF(tbl_text, tbl_text)=1), ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(1, COUNTIF($B$13:B13, INDEX(tbl_text, MIN(IF((COUNTIF($B$13:B13, tbl_text)=0)*(COUNTIF(tbl_text, tbl_text)=1), ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1))+COUNTIF(tbl_text, INDEX(tbl_text, MIN(IF((COUNTIF($B$13:B13, tbl_text)=0)*(COUNTIF(tbl_text, tbl_text)=1), ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1) + CTRL + SHIFT + ENTER copied down as far as necessary.

A range (tbl_num) containing numbers

Array formula in D14:

=SMALL(IF(COUNTIF(tbl_num, tbl_num)=1, tbl_num, ""), ROW(1:1)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges

tbl_text (B2:D4)
tbl_num (B7:D9)
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$13:B13 to F2:$F$2, in the above formula.

Download excel file for this tutorial.

Extract-unique-values-from-a-range-using-array-formulas.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

External resources:
Identifying Unique Values In An Array Or Range (VBA function)

Related posts:

1. Unique distinct values from multiple columns using array formula
2. Extract largest values from two columns using array formula in excel
3. Filter text values existing in range 1 but not in range 2 using array formula in excel
4. Filter common text values in range 1 and in range 2 using array formula in excel
5. Filter unique values from a range using array formula in excel
6. Filter values existing in range 1 but not in range 2 using array formula in excel
7. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
8. Extract duplicates from a range using excel array formula
9. Extract numbers and text from a range using array formula in excel
10. Filter common values between two ranges using array formula in excel