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:

Array formula in cell H2:

=INDEX($C$2:$C$16, MATCH(0, IF($F$1=$B$2:$B$16, COUNTIF($H$1:$H1, $C$2:$C$16), ""), 0))

If you don't want to use an array formula, read this post:

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

How to create an array formula

  1. Copy above array formula
  2. Select cell H2
  3. Click in formula bar
  4. Paste (Ctrl + v)
  5. Press and hold Ctrl + Shift
  6. Press Enter

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

How to copy array formula

  1. Select cell H2
  2. Copy cell (Ctrl +c)
  3. Select cell H3
  4. Paste (Ctrl + v)

create-a-list-of-distinct-values

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