Category: Filtering

Excel Advanced Filter

Posted on by Tushar Mehta

Excel supports two different ways to filter data that are in tabular format. Autofilter is a built-in capability driven via the user interface. As sophisticated as Autofilter has become in recent versions of Excel, no pre-defined setup can possibly cater to all the different questions that the consumer may want answered. These require a custom filter and Advanced Filter provides that capability. It is a data-driven mechanism that uses Excel formulas to extract specific information from the original data. For those who may have heard of SQL but have never been motivated to learn it, you can now leverage some of the power of SQL without learning a single word of SQL!

The layout of this document is as follows: 1) Introduction to the data set used in the examples, 2) Introduction to the Advanced Filter dialog box, 3) Filter using column headers, 4) Filter using Excel formulas, 5) Extract unique data, 6) Work with dynamic source data, and 7) Create a filter in a different worksheet or workbook.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/data_analysis/06.shtml

Tushar Mehta

Copy Unique Values

Posted on by Rob van Gelder

One of the more frequent activities I perform is taking a list and extracting unique values as a seperate list.

The way to do this is long and stupid.
In Excel 2003 you select your range, go to the Data menu, Filter menu, select Advanced Filter. Select “Copy to another location”, tick “Unique records only”, then put a range in the “Copy To” box.
In Excel 2007 and above, it’s one less click. Go to the Data ribbon, from the Sort & Filter group click Advanced, then the same Advanced Filter window appears as Excel 2003.

Sick of this, a few years ago I wrote a macro to extract Unique Values, which worked so-so. My macro had minor issues that I could never really be bothered fixing, but it was miles better than trekking through those menus.
Fast forward to a couple of weeks ago and I lost my macro, somehow, which is disappointing, but it was buggy anyway, but I still wanted an easy way to extract unique values and I didn’t want to write the macro again.
It suddenly occurred to me that the same method of hitting shortcuts keys for paste values (alt, e, s, v) could be applied to copying unique values.

Here’s what I do:
Select the range I want to extract from
Hold down the Alt key
Press these keys in sequence: d, f, a, r, o, t
Release the Alt key
Select the range to paste the unique values to

It’s even possible to create a vba macro for this:
SendKeys "%(dfarot)"

Now, to train my muscle memory to type dfarot naturally…