Join Dennis Taylor for an in-depth discussion in this video Creating custom filters, part of Managing and Analyzing Data in Excel 2010.
In this worksheet consisting of scientific data, in Column B we see codes.…If you work with coding structures you know how strange they can be sometimes.…It just so happens that based on the codes that we're seeing here, we need to…find those that have B in the second position, like this one, and then later the letter Q.…Now, here is a Q right behind it.…There might be some others.…Here is a Q that's a few characters removed.…We want to find all situations where B is the second letter and then later there's a Q.…So let's apply a filter to this data. And click the drop arrow for Code, Text…Filters > Custom Filter.…
Not equals, but begins with, anything, we don't care what it begins with, and…like in our previous example, question mark, then B is the second character.…The asterisk symbol means 0, 1, or many characters.…So after the B, either immediately or one or two or three or four characters…later, a Q is going to cover all those situations.…I'll click OK and in our results we see B in the second position in all of these.…
- Multiple key sorting
- Single and multiple column numeric filters
- Creating a top-ten list with values or percentages
- Setting up subtotals
- Creating multiple-field criteria filters
- Creating unique lists from repeating field data
- Using the Remove Duplicates command
- Finding duplicate data with specialized arrays
- Counting the number of unique items in a list
- Using SUMIF and COUNTIF functions
- Working with the database functions such as DSUM and DMAX
Skill Level Appropriate for all
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.