Article 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

 

Recommended posts

Filter unique distinct values from two ranges combined in excel 2007

Question: How do I filter unique values from two ranges combined using array formulas? Answer: Array formula in B13: =INDEX(_tbl1, […]

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 […]

Filter unique distinct values where adjacent cells contain search string

Question: How do I create a unique distinct list where adjacent cell values contains a search string? AA102 CA AA103 […]

Filter unique distinct text values using “begins with” criterion in a range using array formula in excel

Array formula in B12: =INDEX(tbl, MIN((IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9), ROW(tbl)-MIN(ROW(tbl))+1))), MATCH(0, COUNTIF($B$11:B11, INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, LEN($D$9))=$D$9), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(LEFT(INDEX(tbl, MIN(IF((COUNTIF($B$11:B11, tbl)=0)*(LEFT(tbl, […]

Excel table: Filter unique distinct values (array formula)

This blog post demonstrates how to filter unique distinct values from an excel table dynamically. When you change or add […]

How to filter using OR logic between columns [Advanced Filter]

The built-in filter feature in Excel is a powerful tool, however, it won't allow you to filter with OR logic […]