Here is the pasted version of what I attached as PDF and Excel Model - Chris
Fast Filter Tables in Excel
By Chris O’Neill conw88@gmail.com twitter: conw88
This is a recipe details how to create a “fast filter” for Excel tables. CTRL-F Searching cells is not ideal because I like to line up rows of data that meets the criteria. Excel Tables is an excellent tool which makes this process easier. But I want to combine searching a single term across all data and then filter the table accordingly. The uses for this process are limited only by your imagination. Here are some applications using the Example Data included:
Chandoo posted an excellent Filter-As-You-Type recipe on Chandoo.org. Unfortunately, I use Excel 2011 on an old Mac which does not support ActiveX controls. If you are a windows user, you can combine the directions below and change the macro in the section below called Search_String Trigger to use a TextBox and add Filter-As-You-Type functionality. I plan to do add that when I build my models on Windows machines.
Example Data
We will use the following data set to create the model:
Column1
Column2
Column3
Column4
Column5
FALSE
Monday
CT
Jan
37.59642812
FALSE
Tuesday
MA
Feb
94.40053737
TRUE
Wednesday
CT
Mar
16.38933726
FALSE
Thursday
MA
Apr
85.89681601
FALSE
Friday
CT
May
82.70996471
FALSE
Saturday
MA
Jun
15.41463327
FALSE
Sunday
CT
Jul
52.63184712
FALSE
Monday
MA
Aug
22.29756429
FALSE
Tuesday
MA
Sep
23.68004946
FALSE
Wednesday
MA
Oct
88.87994199
FALSE
Thursday
MA
Nov
25.77727723
FALSE
Friday
MA
Dec
7.200554186
FALSE
Saturday
MA
Jan
84.74721903
FALSE
Sunday
MA
Feb
49.98424324
TRUE
Monday
MA
Mar
21.66058423
FALSE
Tuesday
MA
Apr
38.6367052
FALSE
Wednesday
MA
May
72.12776785
FALSE
Thursday
MA
Jun
62.73151411
FALSE
Friday
MA
Jul
58.69553778
FALSE
Saturday
MA
Aug
29.86045227
FALSE
Sunday
MA
Sep
18.30718289
FALSE
Monday
MA
Oct
95.04766142
FALSE
Tuesday
MA
Nov
40.10858843
FALSE
Wednesday
MA
Dec
29.2078081
FALSE
Thursday
MA
Jan
81.3250472
Requirement #1 - The data must be in the form of an Excel Table. The table should be named “tb_main”. Set the name in the table tab as opposed to the range name tab.
Assign the range name “search_string” to the cell of your choosing. This will be where you type in queries to make the table change.
Row Search Formula
The following formula goes into the first column of the table as it will scan all values in the row to check for the occurrence of value of search_string.
=NOT(ISERROR(MATCH("*"&IF(ISBLANK(search_string),RAND(),search_string)&"*",TEXT(tb_main[@[Column2]:[Column5]],"0.00000"),0)))
Requirement - The Value must be entered as an Array Formula using SHFT-CTRL Enter. The reason is that I am converting all numerical values in the row to strings to search them. If you do not enter it as an array formula you will not search the numerical values. Make sure to clear the entire column before entering updates to the formula. The table will automatically update all blank cells HOWEVER it will not update cells where there is a value already. So you could end up placing a correct array formula in the first row and having something else in the all the other rows.
Optional Reading - Here I Break down what I am doing in the search formula:
=NOT(ISERROR(
‘We are searching the whole row cell by cell to see if we can find our search string.
MATCH(
I added asterisks to each side of the search string so that we capture the string embedded in any other value. If the search_string is “dog”, then this grabs “nicedog” and “doggie”.
"*"&
If search string is blank then search for a random number. This prevents the lack of a search string registering as TRUE across the data set.
IF(ISBLANK(search_string),RAND(),search_string)
This is the other asterix to broaden the search as stated above...
&"*",
This converts each of of the cells in the row to a string out to five decimals. If you want to search values that are more than five decimals deep, just add more zeros to “0.00000”. This is also why the formula must be entered as an Array as only an array can simultaneously search all the cells in a given row.
Change Column 2 and Column 5 to the second and last columns of your table heading names. Remember we are using the first column to contain this formula and want to avois a circular reference.
TEXT(tb_main[@[Column2]:[Column5]],"0.00000")
This is the end of the Match Function where Zero means False or “Exact Search Required.”
,0)))
Macro Code
Clear Filters in Column1 Search
The first macro clears the filter in column 1. This is a free standing macro that does not have any relationship nor is it required to have the system work. Note: If you set your own filters in any of the other columns this macro will not disturb nor change those settings. I did this by design as I often want to search a subset of the data in the table.
Sub tb_main_clear()
' Clear Filters in table tb_main
ActiveSheet.ListObjects("tb_main").Range.AutoFilter Field:=1
Range("search_string").Select
End Sub
Optional - I create a button on the sheet right next to the search_string cell and tie it assign the macro tb_main_clear. I also use the macro options and set the keystroke combo of Option + Command + Z. This makes working with the data a breeze from the keyboard.
Performing the Filtering
The above formula simply shows TRUE if the search_string appears anywhere inside of any number or string in any cell in the row. It will show FALSE if said string doe not appear. Thus, We create the following macro to set the filter once a search_string has been entered into the cell.
Sub tb_main_true()
' Set Filter in table tb_main to a value of true
ActiveSheet.ListObjects("tb_main").Range.AutoFilter Field:=1, Criteria1:="TRUE"
Range("search_string").Select
End Sub
Note: The Range("search_string").Select line is optional. I just like having the cursor end up back in the search box so I can quickly enter another value.
Search_String Trigger
Using the VB Editor under VBAProject > Microsoft Excel Objects > Sheet1, paste the following code. This triggers the macro tb_main_true when the value in search_string is changed by the user.
Private Sub Worksheet_Change(ByVal Target As Range)
'The Subroutine is called when the value in KeyCells Range in quotes below changes.
Dim KeyCells As Range
Set KeyCells = Range("Search_String")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
'Call tb_main_true to filter on values typed into stated key cells range
tb_main_true
End If
End Sub
Known Issues
There are sometimes when typing into the search cell just causes the macro to hang. I have to manually BREAK the code and reset it from the VBA Console.
Large Data Sets with many columns may slow down the array formula in column1.
Future Ideas - I would like to end the code and clear the memory every time on the Mac.
I welcome feedback and improvements.
Chris conw88@gmail.com Twitter / IN : conw88
Fast Filter Tables in Excel
By Chris O’Neill conw88@gmail.com twitter: conw88
This is a recipe details how to create a “fast filter” for Excel tables. CTRL-F Searching cells is not ideal because I like to line up rows of data that meets the criteria. Excel Tables is an excellent tool which makes this process easier. But I want to combine searching a single term across all data and then filter the table accordingly. The uses for this process are limited only by your imagination. Here are some applications using the Example Data included:
- Finding All Records for Monday’s by typing “Mon”.
- Creating a dynamic graph of column 5 numerical data and typing in each Month to get a different chart, and cut and paste each chart into a presentation.
- Finding the occurrence of 389 anywhere in any number.
Chandoo posted an excellent Filter-As-You-Type recipe on Chandoo.org. Unfortunately, I use Excel 2011 on an old Mac which does not support ActiveX controls. If you are a windows user, you can combine the directions below and change the macro in the section below called Search_String Trigger to use a TextBox and add Filter-As-You-Type functionality. I plan to do add that when I build my models on Windows machines.
Example Data
We will use the following data set to create the model:
Column1
Column2
Column3
Column4
Column5
FALSE
Monday
CT
Jan
37.59642812
FALSE
Tuesday
MA
Feb
94.40053737
TRUE
Wednesday
CT
Mar
16.38933726
FALSE
Thursday
MA
Apr
85.89681601
FALSE
Friday
CT
May
82.70996471
FALSE
Saturday
MA
Jun
15.41463327
FALSE
Sunday
CT
Jul
52.63184712
FALSE
Monday
MA
Aug
22.29756429
FALSE
Tuesday
MA
Sep
23.68004946
FALSE
Wednesday
MA
Oct
88.87994199
FALSE
Thursday
MA
Nov
25.77727723
FALSE
Friday
MA
Dec
7.200554186
FALSE
Saturday
MA
Jan
84.74721903
FALSE
Sunday
MA
Feb
49.98424324
TRUE
Monday
MA
Mar
21.66058423
FALSE
Tuesday
MA
Apr
38.6367052
FALSE
Wednesday
MA
May
72.12776785
FALSE
Thursday
MA
Jun
62.73151411
FALSE
Friday
MA
Jul
58.69553778
FALSE
Saturday
MA
Aug
29.86045227
FALSE
Sunday
MA
Sep
18.30718289
FALSE
Monday
MA
Oct
95.04766142
FALSE
Tuesday
MA
Nov
40.10858843
FALSE
Wednesday
MA
Dec
29.2078081
FALSE
Thursday
MA
Jan
81.3250472
Requirement #1 - The data must be in the form of an Excel Table. The table should be named “tb_main”. Set the name in the table tab as opposed to the range name tab.
Assign the range name “search_string” to the cell of your choosing. This will be where you type in queries to make the table change.
Row Search Formula
The following formula goes into the first column of the table as it will scan all values in the row to check for the occurrence of value of search_string.
=NOT(ISERROR(MATCH("*"&IF(ISBLANK(search_string),RAND(),search_string)&"*",TEXT(tb_main[@[Column2]:[Column5]],"0.00000"),0)))
Requirement - The Value must be entered as an Array Formula using SHFT-CTRL Enter. The reason is that I am converting all numerical values in the row to strings to search them. If you do not enter it as an array formula you will not search the numerical values. Make sure to clear the entire column before entering updates to the formula. The table will automatically update all blank cells HOWEVER it will not update cells where there is a value already. So you could end up placing a correct array formula in the first row and having something else in the all the other rows.
Optional Reading - Here I Break down what I am doing in the search formula:
=NOT(ISERROR(
‘We are searching the whole row cell by cell to see if we can find our search string.
MATCH(
I added asterisks to each side of the search string so that we capture the string embedded in any other value. If the search_string is “dog”, then this grabs “nicedog” and “doggie”.
"*"&
If search string is blank then search for a random number. This prevents the lack of a search string registering as TRUE across the data set.
IF(ISBLANK(search_string),RAND(),search_string)
This is the other asterix to broaden the search as stated above...
&"*",
This converts each of of the cells in the row to a string out to five decimals. If you want to search values that are more than five decimals deep, just add more zeros to “0.00000”. This is also why the formula must be entered as an Array as only an array can simultaneously search all the cells in a given row.
Change Column 2 and Column 5 to the second and last columns of your table heading names. Remember we are using the first column to contain this formula and want to avois a circular reference.
TEXT(tb_main[@[Column2]:[Column5]],"0.00000")
This is the end of the Match Function where Zero means False or “Exact Search Required.”
,0)))
Macro Code
Clear Filters in Column1 Search
The first macro clears the filter in column 1. This is a free standing macro that does not have any relationship nor is it required to have the system work. Note: If you set your own filters in any of the other columns this macro will not disturb nor change those settings. I did this by design as I often want to search a subset of the data in the table.
Sub tb_main_clear()
' Clear Filters in table tb_main
ActiveSheet.ListObjects("tb_main").Range.AutoFilter Field:=1
Range("search_string").Select
End Sub
Optional - I create a button on the sheet right next to the search_string cell and tie it assign the macro tb_main_clear. I also use the macro options and set the keystroke combo of Option + Command + Z. This makes working with the data a breeze from the keyboard.
Performing the Filtering
The above formula simply shows TRUE if the search_string appears anywhere inside of any number or string in any cell in the row. It will show FALSE if said string doe not appear. Thus, We create the following macro to set the filter once a search_string has been entered into the cell.
Sub tb_main_true()
' Set Filter in table tb_main to a value of true
ActiveSheet.ListObjects("tb_main").Range.AutoFilter Field:=1, Criteria1:="TRUE"
Range("search_string").Select
End Sub
Note: The Range("search_string").Select line is optional. I just like having the cursor end up back in the search box so I can quickly enter another value.
Search_String Trigger
Using the VB Editor under VBAProject > Microsoft Excel Objects > Sheet1, paste the following code. This triggers the macro tb_main_true when the value in search_string is changed by the user.
Private Sub Worksheet_Change(ByVal Target As Range)
'The Subroutine is called when the value in KeyCells Range in quotes below changes.
Dim KeyCells As Range
Set KeyCells = Range("Search_String")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
'Call tb_main_true to filter on values typed into stated key cells range
tb_main_true
End If
End Sub
Known Issues
There are sometimes when typing into the search cell just causes the macro to hang. I have to manually BREAK the code and reset it from the VBA Console.
Large Data Sets with many columns may slow down the array formula in column1.
Future Ideas - I would like to end the code and clear the memory every time on the Mac.
I welcome feedback and improvements.
Chris conw88@gmail.com Twitter / IN : conw88