Table of contents

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

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)

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Filter unique distinct text values in a range using “contain” condition in excel
  2. Filter unique text values in a range using “contain” condition in excel
  3. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
  4. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
  5. Filter unique distinct values from two ranges combined in excel 2007
  6. Highlight unique values and unique distinct values in a range using conditional formatting in excel
  7. Filter unique distinct values where adjacent cells contain search string in excel
  8. Filter duplicate values in a range using “contain” condition in excel
  9. Filter a column and create a new unique list sorted from A to Z using array formula in excel
  10. How to extract a unique distinct list of a column in excel