Smart Filter

[jdecastro]

Hi,

I need to have what I call a smart Filter.

If I have 3 Columns and they all have values such as

1 2 3
2 2 3
2 1 3
3 3 3
3 1 1

I want to have a filter in which I only have to filter one column for the number 1, and have a filter for all three columns:

1 2 3
2 1 3
3 1 1

Thanks

JDC

 

[mikerickson]

Consider this set-up.
The data is in columns A:C
The user inputs a 1 into E2.
G2, H3 and I4 all contain the formula =E2
AdvancedFilter with the List range A1:C6 and the CriteriaRange G1:I4 will filter the rows that you want.

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b><td width=25><b>G</b><td width=25><b>H</b><td width=25><b>I</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>First Header<td align="left" bgcolor=#FFFFFF>Second Header<td align="left" bgcolor=#FFFFFF>Third Header<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Filter by<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>First Header<td align="left" bgcolor=#FFFFFF>Second Header<td align="left" bgcolor=#FFFFFF>Third Header</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="right" bgcolor=#FFFFFF>1<td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>1<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>1<td align="right" bgcolor=#FFFFFF>1<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>

 

[hiker95]

jdecastro,


Welcome to the MrExcel forum.


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:
2	1	2	3
3	2	2	3
4	2	1	3
5	3	3	3
6	3	1	1
7
Sheet1

If we enter a 1 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	1
2	1	2	3
4	2	1	3
6	3	1	1
7
Sheet1

If we enter a 2 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	2
2	1	2	3
3	2	2	3
4	2	1	3
7
Sheet1

If we enter a 3 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	3
2	1	2	3
3	2	2	3
4	2	1	3
5	3	3	3
6	3	1	1
7
Sheet1

If we enter a 4 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	4
7
Sheet1

If we delete what is in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:
2	1	2	3
3	2	2	3
4	2	1	3
5	3	3	3
6	3	1	1
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Right click the sheet tab you want the code in (in this example, Sheet1), and click on View Code. Paste the below code there (on the right pane) by pressing the keys CTRL + V
4. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 10/23/2010, ME503884
If Intersect(Target, Range("F1")) Is Nothing Then
  Exit Sub
Else
  Call Test
End If
End Sub

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Sub Test()
' hiker95, 10/23/2010, ME503884
Dim LR As Long, a As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
  .UsedRange.Rows.Hidden = False
  If .Range("F1") = "" Then
    Application.ScreenUpdating = True
    MsgBox "There is no search data in cell F1 - macro terminated!"
  Else
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    If LR > 1 Then
      For a = LR To 2 Step -1
        If Application.CountIf(.Range("A" & a & ":C" & a), .Range("F1")) = 0 Then .Rows(a).Hidden = True
      Next a
    Else
      Application.ScreenUpdating = True
      MsgBox "There is no raw data in column A - macro terminated!"
    End If
  End If
End With
End Sub

Then make changes to cell F1 in worksheet Sheet1.

 

[Peter_SSs]

Welcome to the MrExcel board!

My effort assumes ..

- Data starts in A1
- Headings in row 1
- No completely empty rows or columns within the data to be filtered
- The value to filter on is entered into F1 per Hiker95's model

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Enter/delete values in F1


<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rData <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rData = Range("A1").CurrentRegion<br>    myVal = Range("F1").Value<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> myVal = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>        rData.EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        LR = rData.Rows.Count<br>        <SPAN style="color:#00007F">For</SPAN> r = 2 <SPAN style="color:#00007F">To</SPAN> LR<br>            Rows(r).Hidden = Intersect(rData, Rows(r)).Find(What:=myVal, _<br>                LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False) _<br>                <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> r<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[jdecastro]

It didn't work... I did exactly that and it didn't work

Am I missing something?

Consider this set-up.
The data is in columns A:C
The user inputs a 1 into E2.
G2, H3 and I4 all contain the formula =E2
AdvancedFilter with the List range A1:C6 and the CriteriaRange G1:I4 will filter the rows that you want.

<TABLE cellSpacing=0 border=1>
<TBODY><TR align=middle bgColor=#a0a0a0><TD width=25> <TD width=25>A<TD width=25>B<TD width=25>C<TD width=25>D<TD width=25>E<TD width=25>F<TD width=25>G<TD width=25>H<TD width=25>I</TD>

<TR><TD align=middle bgColor=#a0a0a0>1<TD align=left bgColor=#ffffff>First Header<TD align=left bgColor=#ffffff>Second Header<TD align=left bgColor=#ffffff>Third Header<TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff>Filter by<TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff>First Header<TD align=left bgColor=#ffffff>Second Header<TD align=left bgColor=#ffffff>Third Header</TD>

<TR><TD align=middle bgColor=#a0a0a0>2<TD align=right bgColor=#ffffff>1<TD align=right bgColor=#ffffff>2<TD align=right bgColor=#ffffff>3<TD align=left bgColor=#ffffff><TD align=right bgColor=#ffffff>1<TD align=left bgColor=#ffffff><TD align=right bgColor=#ffffff>1<TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff></TD>

<TR><TD align=middle bgColor=#a0a0a0>3<TD align=right bgColor=#ffffff>2<TD align=right bgColor=#ffffff>2<TD align=right bgColor=#ffffff>3<TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=right bgColor=#ffffff>1<TD align=left bgColor=#ffffff></TD>

<TR><TD align=middle bgColor=#a0a0a0>4<TD align=right bgColor=#ffffff>2<TD align=right bgColor=#ffffff>1<TD align=right bgColor=#ffffff>3<TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=right bgColor=#ffffff>1</TD>

<TR><TD align=middle bgColor=#a0a0a0>5<TD align=right bgColor=#ffffff>3<TD align=right bgColor=#ffffff>3<TD align=right bgColor=#ffffff>3<TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff></TD>

<TR><TD align=middle bgColor=#a0a0a0>6<TD align=right bgColor=#ffffff>3<TD align=right bgColor=#ffffff>1<TD align=right bgColor=#ffffff>1<TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff><TD align=left bgColor=#ffffff></TD>

</TR>
</TABLE>

 

[mikerickson]

If your screen looks like that and the arguments used by AdvancedFilter are as written, it should work. There must be no blank rows in the criteria range.

You say "didn't work". What does that mean? It did nothing? (Probably a blank criteria row) It gave you an error message? (If so, which one)

 

[jdecastro]

Thanks for the prompt response,

It filtered everything, I typed one, and showed none, I typed two, and showed none, I typed 16 and showed none. After everything is filtered I erase the filter box and nothing happens... There are no error boxes, nor any other actions.

Thanks

If your screen looks like that and the arguments used by AdvancedFilter are as written, it should work. There must be no blank rows in the criteria range.

You say "didn't work". What does that mean? It did nothing? (Probably a blank criteria row) It gave you an error message? (If so, which one)

 

[mikerickson]

AdvancedFilter takes a data range and shows only those rows that meet the criteria.
It does NOT dynamicaly filter as data is added to the range. After entering new data (or changing the criteria for filtering), the user must reapply AdvancedFilter in order for the filter to work

 

[Peter_SSs]

It didn't work...

Did you try either of the other suggestions?

 

[jdecastro]

I tried hiker 95 and peter sss's suggestions and they both worked perfectly. I went with hiker 95's suggestion in the work that I'm doing due to the fact that it fitted perfectly with what I wanted.

Thank you all