Using the AutoFilter/Custom Filter function in Excel

[davey11372]

<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cdevray1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> I am trying to use the AutoFilter/Custom function in Excel (it is available under the Data Menu). It offers me two conditions/criteria that I can apply using and/or. For eg:
<o> </o>
Filter:
<o> </o>
does not begin with - 3
<o> </o>
and / or
<o> </o>
does not begin with - 9.
<o> </o>
I want to add a third 'and' criteria .. is it possible, and if so, how?
<o> </o>
The column that I am trying to filter has numbers formatted as text.

 

[Beate Schmitz]

Hello Davey,

use a helpcolumn with formula for filtering and filter for TRUE, here for textvalues not beginning with 3, 6 and 9:

Excel Workbook
	A	B
1	Value1	Helpcolumn
2	6897	FALSE
3	7653	TRUE
4	323	FALSE
5	15673	TRUE
6	9681	FALSE
7	891	TRUE
Sheet

Formula of B2 can be copied down.

 

[davey11372]

Thank you Beate. Appreciate your help.

I would like to know if I could take the following approach, and if yes, how/what code to write.

I am currently using the following script/macro for the custom filter function.
...................................................................
Sub newFilterPaste2c()
'
' newFilterPaste2c Macro
' Macro recorded 1/30/2010 by devray
'
' Keyboard Shortcut: Ctrl+z
'
Columns("J:J").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>3*", Operator:=xlAnd, _
Criteria2:="<>9*"
Range("K2:K31501").Select
Selection.copy
End Sub
......................................................................................
The line that is highlighted in blue, is it possible to include another criteria, for eg:

Selection.AutoFilter Field:=1, Criteria1:="<>3*", Operator:=xlAnd, _
Criteria2:="<>9*, Operator:=xlAnd, Criteria3:="<>4*"

In fact, I am going to try it. But if the code needs to be something else, I would appreciate if someone can let me know. If it works, I will post so.

Thank you.

 

[davey11372]

Tried it, the code for the additional criteria does not work.

 

[Beate Schmitz]

Thats right, autofilter only can handle 2 criterias. That's why I recommend a helpcolumn with formula as shown above.

 

[davey11372]

I should be able to work with the method you suggested. Thank you.

 

[lenze]

Maybe Advanced Filter??
lenze

 

[davey11372]

Thanks for the suggestion Lenze. I have not been able to make the Advanced Filter work for what I am trying to do. I am not sure if I am applying it correctly. May be you could explain the steps to follow. I will try it again though.

 

[jeffreybrown]

Is there something here you can find that might help...

http://contextures.com/xladvfilter01.html

 

[davey11372]

Surely helps Jeff. Thanks much.