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

Answer:

Extract-unique-values-from-a-range

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)