Filter a list of items from a long long list

A simple but practical tip of using Advanced Filter

I came across a blogpost from Jon of Excel Campus (one of my favorite Excel sites) last week.  In his post, he showed how to filter for a list of items using a reverse partial match lookup.  Obviously, it is a formula approach.  From his post, you will see how Excel formula can save you from tedious works at workplace.

In this post, I am going to replicate Jon’s example and achieve similar results without a single formula.  Thanks to Advanced Filter!

Where is Advanced Filter?

Yes.  It’s right there – just next to the Filter that most people have used it.

Excel Tips - Advanced Filter

But… I believe there is a high chance that you may have never used it before… 🙂  If that’s the case, please continue to read and you will find it super helpful.

Although with the term “Advanced”, Advanced Filter is indeed not difficult to use at all.

You may download a Sample File here to follow through.

Situation

Excel Tips - Advanced Filter 1

What we have is a list of 1000+ company names.  What we need is a list of companies that contains the word “bank” or “Telecom”.  You may wonder why we don’t just do it with normal Filter….

How to do it with a Filter?

1) The silly way

As I said, it is a tedious work.  Some people will browse the list from the filter and click the check box one by one, by eyeball.  Yes by eyeball.

Excel Tips - Advanced Filter

Seriously?  No way… @_@

2) Using the Search box in Filter

Well, if you are an experienced user of Filter, you know that you may search for what you need by inputting the key words in the search box.  See below:

While you input “bank”, you will see immediately the filter is smart enough to show you only items that contain “bank”.  What’s is even better is you can ADD other search terms to your filter list – by checking the “Add current selection to filter“.

Excel Tips - Advanced Filter 1

WOW, that’s good enough you may think.  Well… if we are dealing with only two items, yes it is.  Think about you need to deal with 10 items?  Then you need to repeat the steps 9 times.  That is tedious.  What’s worse?  The frustrating moment comes when you forget to check the “Add current selection to filter” on your final search term.  You know what I mean.

3) The proper way – Advanced Filter

Setting up

Before it works, we need to set a “Criteria Range” to tell Excel the list of names that we want.  Let’s set the Criteria using D1:D3

Excel Tips - Advanced Filter 2.PNG

Note: The title of the Criteria Range, i.e. D1 in the above example, must be identical to the tile on the column we want to filter, i.e. B1 in the above example.

In action

Go to Data tab –> Sort & Filter –> Advanced

Excel Tips - Advanced Filter

Excel Tips - Advanced Filter 3

For demonstration purpose, I select “Copy to another location”.  You may, of course, “Filter the list, in place”, i.e in the original source location.

Here we go…

Excel Tips - Advanced Filter 4

Wait… the matching list should be longer…

Apparently, the Advanced Filter only retreturnsmpany names that begin with “Bank” or “Telecom”…

A little twist – Using wildcard *

Excel Tips - Advanced Filter 5

By wrapping the text string with asterisk (*), Excel now interprets the criteria as anything that contains “bank” or “telecom”.

Tip: * is the wildcard for any number of characters

Here we go…

Excel Tips - Advanced Filter 6

It is done and as simple as this…… if you need a partial match.  What if you want to differentiate Telecom from Telecommunications?

As you see from the above result, company names with “Telecom” and “Telecommunications” got filter altogether.  Indeed, that is what we instructed Excel to do… filter those contain the text string “telecom”.

To get the names with the word “Telecom” but not “Telecom*”, you will need an intermediate step.

Another twist – Adding space before & after the text string ” Telecom “

Excel Tips - Advanced Filter 6.1

This simple twist instructs Excel to look for (space)telecom(space).  With this trick, the word “telecommunications” does not match the filter criteria.  Of course, this twist assumes each word be separated by space.

So are we done? Not yet… as the trick also excludes two possible scenarios:

  1. Names start with “Telecom “
  2. Names end with ” Telecom”

Here comes the Advanced part

To cater for the above scenarios, we need to add two more criteria into B4:B5.  That means we need to extend the Criteria range to D5.  See below:

Excel Tips - Advanced Filter 7

Note: In the sample file, you will see the filtered result at the end with two dummy names, one starts with Telcom; one ends with Telcom.

To be exact, the following are input as criteria:

B2 - *bank* 'Contains the string bank (any position)
B3 - * telecom * 'Contains the word telecom (in between only)
B4 - ="=Telecom *" 'Starts with the word Telecom
B5 - ="=*Telecom" 'Ends with the word Telecom
Note: The * at the end is not necessary.  I put it there to make the last space visible

The input for B4 and B5 are not straightforward.  Nevertheless, once you learn the construction and do some practices, it will turn into another piece of cake… 🙂

To Recap

If you need to filter for a list of items (strings) regardless position in the source data, i.e. partial match, you may use Advanced Filter to do the job easily, as long as you remember to put * before the strings in your criteria range.  E.g.

*bank
*telecom
*technology
*holdings

However, if your requirement is a bit complicated, you may need to know the special construction for

="=ABC" 'Contains ABC only
="=ABC*" 'Starts with the string ABC
="=*ABC" 'Ends with the string ABC
="=ABC *" 'Starts with the word ABC; note the space
="=* ABC" 'Ends with the word ABC; note the space

Final note: Advanced Filter is case-insensitive

Please download the Sample File to practise.

Advertisements
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

5 Responses to Filter a list of items from a long long list

  1. Sabine says:

    Hello MF,
    I like the filter functions pretty much and use them very often. They are really great! But in older versions the way is a little bit different. You get Auto Filter on Data -> Auto Filter. Then mark the headline of your table. Select a column and click on “customized”. And there you go! You may select your search term under “contains” or “does not contain” in combination with an ‘*’ or ‘?’. It’s a real great thing. And one of the best is, that you can save your filtered view and use it the next time whenever it is needed. Very handy, if you are working with data requests.

    Sabine

    Like

  2. excelesquire says:

    Great solution and great post! I’ve tackled this kind of problem with a SUMPRODUCT formula, but this is a much more straightforward approach.

    Like

  3. Billy says:

    Hey, great post! I often forget that advanced filtering is there but it’s so useful.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s