Question: How do I create a distinct list from column C (Values) where cell values in column B (Category) equals the value AA? See picture below.

create-a-list-of-duplicates2

Answer: See picture below.

Formula displaying item numbers (H2:H16):

=IF(ROW()-1>SUM(IF($F$1=$B$2:$B$16, IF(MATCH($B$2:$B$16&$C$2:$C$16, $B$2:$B$16&$C$2:$C$16, 0)=ROW($B$2:$B$16)-1, 1, ""), "")), "", SMALL(IF($F$1=$B$2:$B$16, IF(MATCH($B$2:$B$16&$C$2:$C$16, $B$2:$B$16&$C$2:$C$16, 0)=ROW()-1, ROW()-1, ""), ""), ROW()-1)) + CTRL + SHIFT + ENTER

Formula displaying categories (I2:I16):

=IF(ROW()-1>SUM(IF($F$1=$B$2:$B$16, IF(MATCH($B$2:$B$16&$C$2:$C$16, $B$2:$B$16&$C$2:$C$16, 0)=ROW($B$2:$B$16)-1, 1, ""), "")), "", INDEX($B$2:$B$16, $H$2:$H$16)) + CTRL + SHIFT + ENTER

Formula displaying values (J2:J16):

=IF(ROW()-1>SUM(IF($F$1=$B$2:$B$16, IF(MATCH($B$2:$B$16&$C$2:$C$16, $B$2:$B$16&$C$2:$C$16, 0)=ROW($B$2:$B$16)-1, 1, ""), "")), "", INDEX($C$2:$C$16, $H$2:$H$16))

create-a-list-of-distinct-values

Download excel example file.
create-a-list-of-distinct-values-where-adjacent-cell-value-meets-criteria.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

SUM(number1,[number2],)
Adds all the numbers in a range of cells

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