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:

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)