Sunday, September 30, 2012

How To Filter An Excel Table On Multiple Columns Using VBA

Filtering information out of a larger data set is standard Excel functionality but sometimes it's necessary to fine tune the filtering to get the information you need.

For example, you might have a list of customers prioritized as A,B or C. Additionally, you could have them listed by region. Something like this.

Customer, Category, Country

ABC Limited, A, USA

ZYZ Trading, B, UK

IJK Co.,A, Australia

You can use the filtering tool to select, for example all the A category customers or those residing in the UK or Australia. But you might want to select all customers that are rated A OR live in the USA.

An Excel Solution To Filtering Multiple Columns

One solution is to create an additional column holding a formula to identify customers matching the criteria. The formula might be similar to this one:

=IF(OR(b2="A",c2="USA"),1,0)

The new column can then be filtered to extract the data required.

A VBA Alternative To Multiple Column Filters

If the thought of multi-bracketed formulas doesn't inspire you, a few lines of VBA code might do the job just as well. We'll create some code which will select customers that are rated "A" OR live in the USA.

First, select the first column and create a string to hold the search criteria.

Set r = ActiveCell.CurrentRegion.Columns(2)

searchStr = ",USA,A"

It's good practice when using a string for searching to enclose each item in a delimiter. For example ",UK," and not "UK". Otherwise, a search for "England" might return matches for "New England"

Now we can loop through each row to see if there is a match on either of our parameters.

For x = 2 To r.Rows.Count

item1 = "," & r.Rows(x) & ","

item2 = "," & r.Rows(x).Offset(0, 1) & ","

If there isn't a match on the row, then we hide the entry.

If InStr(searchStr, item1) = 0 And InStr(searchStr, item2) = 0 Then

r.Rows(x).EntireRow.Hidden = True
End If

To "unhide" the rows, we'll use a decision box and reverse the hide command.

unhide = MsgBox("Show hidden rows?", vbYesNo)

If unhide = 6 Then
For x = 2 To r.Rows.Count
r.Rows(x).EntireRow.Hidden = False
Next
End If

With some development the code would work equally as well with more columns, or you could use VBA to code a formula to match the criteria and then filter the new column.

Summary

This small code snippet uses standard programming techniques to filter a table by applying criteria across multiple columns. It's another example of how a little knowledge of VBA and Excel can improve your productivity many times over.


No comments:

Post a Comment