Filter unique distinct values if value contains specific string [Formula and Advanced Filter]
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:
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)
- Select the range (A1:A20)
- Click "Data" tab on the ribbon
- Click "Advanced" on the ribbon
- Click "Copy to another location"
- Select A1:A20 in "List range:"
- Click C1 in "Copy to:"
- Click "Unique records only"
- Click OK!
- Select C2
- Click "Filter" on the ribbon
- Click "Black triangle" in cell C1
- Click "Text Filters"
- Click "Contains..."
- Type "r" in "contains" window
- 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
Array formula in cell C3:
How to create an array formula
- Select cell C3
- Click in formula bar
- Copy and paste formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
- Release all keys
How to copy array formula
- Select cell C3
- Copy cell C3 (Ctrl + c)
- Select cell range C3:C6
- Paste (Ctrl + v)
Download excel *.xlsx file
extract unique distinct values beginning with a letter.xlsx
Lookup with multiple criteria and return multiple search results
Question: How do I search a list containing First name column and a last name column? I want to search […]Search for a text string and return multiple adjacent values
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]6 Responses to “Filter unique distinct values if value contains specific string [Formula and Advanced Filter]”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
I have a data in same way only difference is that they are in number format i.e. (330-1541) in this way so is there any way i can use the filter for such type of data. Please help me.
Ashok,
did you create an array formula?
[...] For Extracting Records From Data Set (12 Examples) - YouTube Bill Jelen - YouTube Or here... Filter unique distinct values using “contain” condition of a column in excel | Get Digit... I hope this helps. [...]
Do you have examples for using advanced filter for "Not Contains"?
I have a data of this sort...
Probe ID Call ID
USBE1 130226200131-1
USBE1 130226200131-2
USBE1 130226200131-3
USBE1 130226200131-4
USBE1 130226200521-1
USBE1R1 130227143154-1
USBE1R1 130227143154-10
USBE1R1 130227143154-11
USBE1R1 130227143154-12
USBE1R1 130227143154-13
USBE1R1 130227143154-14
USBE1R1 130227143154-15
..The "" condition works on the first column, but not on the second column..I am treating both as text columns. But the advanced filter for "Not" condition on these are not working..any help?
dashil103,
Sorry I don´t understand. What is the desired output?
I would like to do something vaguely similar to this. I would like to filter a list with tho columns. In the first column is an ID ("List 1", "List 2", "List 3", and so on), in the second one is a value. Now i would like to list all the values where the first row contains "List 1". Could you help me with this?