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.
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