Author: Oscar Cronquist Article last updated on February 19, 2018

Table of contents

Filter unique distinct values using a "contain" condition
Filter unique distinct values using a "contain" condition (Advanced filter)
Filter unique distinct values beginning with a letter

Filter unique distinct values using "contain" condition of a column in excel (array formula)

Array formula in C2:

=INDEX(List, SMALL(IF(ISNUMBER(SEARCH($B$2, List))*NOT(COUNTIF(C1:$C$1, List)), ROW(List)-MIN(ROW(List))+1, ""), 1)) + CTRL + SHIFT + ENTER

copied down as far as needed.

Named ranges

List (A2:A20)
What is named ranges?

Download excel sample file for this tutorial

Filter unique distinct values using contain condition from a column.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

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

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

NOT(logical)
Changes FALSE to TRUE, or TRUE to FALSE

Filter unique distinct values using "contain" condition of a column in excel (Advanced filter)

  1. Select the range (A1:A20)
  2. Click "Data" tab on the ribbon
  3. Click "Advanced" on the ribbon
  4. Click "Copy to another location"
  5. Select A1:A20 in "List range:"
  6. Click C1 in "Copy to:"
  7. Click "Unique records only"
  8. Click OK!
  9. Select C2
  10. Click "Filter" on the ribbon
  11. Click "Black triangle" in cell C1
  12. Click "Text Filters"
  13. Click "Contains..."
  14. Type "r" in "contains" window
  15. Click ok!

Download excel sample file for this tutorial

Select Sheet "Adv. Filter

Filter unique distinct values using contain condition from a column.xls
(Excel 97-2003 Workbook *.xls)

Filter unique distinct values beginning with a letter

extract unique distinct values beginning with

Array formula in cell C3:

=INDEX($A$1:$A$10,MATCH(0,COUNTIF($C$2:C2,$A$1:$A$10)+((LEFT($A$1:$A$10,1)=$E$2)=FALSE),0))

How to create an array formula

  1. Select cell C3
  2. Click in formula bar
  3. Copy and paste formula to formula bar
  4. Press and hold Ctrl + Shift
  5. Press Enter
  6. Release all keys

How to copy array formula

  1. Select cell C3
  2. Copy cell C3 (Ctrl + c)
  3. Select cell range C3:C6
  4. Paste (Ctrl + v)

Download excel *.xlsx file

extract unique distinct values beginning with a letter.xlsx

 

Related articles

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Create a unique distinct list using Advanced Filter in a macro [VBA]

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]