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. Filter text values existing in range 1 but not in range 2 using array formula in excel
2. Filter unique values from a range using array formula in excel
3. Filter common text values in range 1 and in range 2 using array formula in excel
4. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
5. Extract numbers and text from a range using array formula in excel
6. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
7. Extract duplicate text values from a range containing both numerical and text values in excel
8. Create unique distinct list sorted based on text length using array formula in excel
9. Sort values in parallel (array formula)
10. Filter unique text values in a range using “contain” condition in excel