Filter Excel Data using vba Multiple Columns Multiple Criteria

[pmyk]

Filter Excel Data using vba Multiple Columns Multiple Criteria
How to filter records having
Name: Ben.
Address data in any of the 3 columns : either "School" or "College" or "Office"
within zipcode : 254365.
After using this filter, data of all the three matching IDs i.e.,
1111, 1112, 1114 should be displayed in the filter and ID 1113 should be omitted.
Please help. Thanks in advance.


ColA ColB ColC ColD ColE ColF
1111 - Ben - H.O.D. - Dept of Physics - New College - 254365
1112 - Benny - Manager - Corporate Office - Mount Road - 254365
1113 - Benson - Benson Industry - Sector-G - Industrial Estate - 254365
1114 - Benjamin - Grand School - Cross Road - Old Town - 254365

[streub]

Greetings pmyk!


Including a copy of your project would be helpful.

[pmyk]

My code filters all matching records with the zip number.
Then the next line filters the Name within the filtered.
Then the next line filters School within the filtered.
So, the records with the text College and Office are not there in the filtered list.
The filtering portion of my code:

With rng
 .AutoFilter Field:=5, Criteria1:="*" & Trim(varZip) & "*"     
 .AutoFilter Field:=2, Criteria1:="*" & Trim(varNam) & "*"
 .AutoFilter Field:=3, Criteria1:="*" & "School" & "*", _
   Operator:=xlOr, Criteria2:="*" & "College" & "*"
 .AutoFilter Field:=4, Criteria1:="*" & "School" & "*", _
   Operator:=xlOr, Criteria2:="*" & "College" & "*"
 .AutoFilter Field:=5, Criteria1:="*" & "School" & "*", _
   Operator:=xlOr, Criteria2:="*" & "College" & "*"
End With

I dont know how to use the following code, in case it has to be used:

 .AutoFilter Field:=2, Criteria1:=Array( _
        "*" & "School" & "*", "*" & "College" & "*", "*" & "Office" & "*"), Operator:=xlFilterValues

Actually, the searching words which are School, College and Office will be received in variables and only the variables will be used in the above code. But just for testing purposes I directly entered those searching Texts in the above code itself.
Suggestions appreciated. Thanks.

[SamT]

Here's an idea. It is not tested in any way and probably won't work as is, but...

Option Compare Text
'See Help for "Like"

Sub SamT()
Dim i As Long
Dim Location As String
Location = "Office"

Dim LastRow As Long
For i = 1 To LastRow
With Rows(i)
If Cells(2) Like ("!Ben*") Then
  .Hidden = True
ElseIf Cells(4) Like ("!*School*") _
  And Cells(4) Like ("!*College*") _
  AndCells(4) Like ("!*" & Location & "*") Then
  .Hidden = True
ElseIf Cells(6) Like ("!" & ZipCode) Then
  .Hidden = True
End If
End With
Next


End Sub

[pmyk]

Thanks. I will try it out.

[p45cal]

For others, cross-posted at http://www.mrexcel.com/forum/excel-q...-criteria.html
For pmyk/pmich, see attached, wheerin macros:

Sub Macro1()
ss = "Ben"
tt = "254365"
uu = "College"
vv = "School"
ww = "Office"
    Range("I15").Formula = "=AND(SEARCH(""" & ss & """,B2)=1,F2=""" & tt & """,OR(ISNUMBER(SEARCH(""" & vv & """,C2)),ISNUMBER(SEARCH(""" & uu & """,C2)),ISNUMBER(SEARCH(""" & ww & """,C2)),ISNUMBER(SEARCH(""" & vv & """,D2)),ISNUMBER(SEARCH(""" & uu & """,D2)),ISNUMBER(SEARCH(""" & ww & """,D2)),ISNUMBER(SEARCH(""" & vv & """,E2)),ISNUMBER(SEARCH(""" & uu & """,E2)),ISNUMBER(SEARCH(""" & ww & """,E2))))"
    'Range("I15").Formula = "=AND(SEARCH(""Ben"",B2)=1,F2=""254365"",OR(ISNUMBER(SEARCH(""School"",C2)),ISNUMBER(SEARCH(""College"",C2)),ISNUMBER(SEARCH(""Office"",C2)),ISNUMBER(SEARCH(""School"",D2)),ISNUMBER(SEARCH(""College"",D2)),ISNUMBER(SEARCH(""Office"",D2)),ISNUMBER(SEARCH(""School"",E2)),ISNUMBER(SEARCH(""College"",E2)),ISNUMBER(SEARCH(""Office"",E2))))"
    Range("A1:F5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("I14:I15"), CopyToRange:=Range("P1"), Unique:=False
End Sub

and

Sub Macro3()
ss = "Ben"
tt = "254365"
uu = "College"
vv = "School"
ww = "Office"
    Range("I15").Formula = "=AND(SEARCH(""" & ss & """,B2)=1,F2=""" & tt & """,OR(ISNUMBER(SEARCH(""" & vv & """,C2)),ISNUMBER(SEARCH(""" & uu & """,C2)),ISNUMBER(SEARCH(""" & ww & """,C2)),ISNUMBER(SEARCH(""" & vv & """,D2)),ISNUMBER(SEARCH(""" & uu & """,D2)),ISNUMBER(SEARCH(""" & ww & """,D2)),ISNUMBER(SEARCH(""" & vv & """,E2)),ISNUMBER(SEARCH(""" & uu & """,E2)),ISNUMBER(SEARCH(""" & ww & """,E2))))"
    'Range("I15").Formula = "=AND(SEARCH(""Ben"",B2)=1,F2=""254365"",OR(ISNUMBER(SEARCH(""School"",C2)),ISNUMBER(SEARCH(""College"",C2)),ISNUMBER(SEARCH(""Office"",C2)),ISNUMBER(SEARCH(""School"",D2)),ISNUMBER(SEARCH(""College"",D2)),ISNUMBER(SEARCH(""Office"",D2)),ISNUMBER(SEARCH(""School"",E2)),ISNUMBER(SEARCH(""College"",E2)),ISNUMBER(SEARCH(""Office"",E2))))"
    Range("A1:F5").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("I14:I15"), Unique:=False
End Sub

the only difference being one filters in-place.

[pmyk]

Thanks. I will surely try it.
To get a quick reply, I cross posted.
Thanks for mentioning that link.

[pmyk]

Thanks SamT for the code using HIDDEN.
I will be using it.


Thanks p45cal for the code using SEARCH and ISNUMBER.
I will be using it.


I searched the web and found usage of AdvancedFilter, which I have given below. It also filters SCHOOL, CoLLege and OfficE. Moreover it is faster.


Set rng = ActiveSheet.Range("D1:J" & Trim(Str(LstNumRow))) 'D-Names. E- Adrs1. F-Adrs2.G-Adrs3.H-City. I-State. J-Zip.


N1 -T10 has Filter Criteria with actual column headings of the source in Row N1.
Name - Adrs1 - Adrs2 - Adrs3 - Zip
*Ben* - *College* - - - 254365
*Ben* - *Office* - - - 254365
*Ben* - *School* - - - 254365
*Ben* - - *College* - - 254365
*Ben* - - *Office* - - 254365
*Ben* - - *School* - - 254365
*Ben* - - - *College* - 254365
*Ben* - - - *Office* - 254365
*Ben* - - - *School* - 254365


rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("N1:T10"), Unique:=False
Thanks for your valuable suggestions.
I have learnt new ideas. Appreciate your help.

[pmyk]

Thanks p45cal for the attachment with the usage of AdvancedFilter. Appreciate your help.