PDA

View Full Version : [SOLVED] Filter Excel Data using vba Multiple Columns Multiple Criteria



pmyk
10-23-2013, 03:23 AM
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
10-23-2013, 04:01 AM
Greetings pmyk!


Including a copy of your project would be helpful.

pmyk
10-23-2013, 04:30 AM
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
10-23-2013, 08:43 AM
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
10-24-2013, 10:00 AM
Thanks. I will try it out.

p45cal
10-24-2013, 02:08 PM
For others, cross-posted at http://www.mrexcel.com/forum/excel-questions/734460-filter-excel-data-using-visual-basic-applications-multiple-columns-multiple-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
10-24-2013, 08:44 PM
Thanks. I will surely try it.
To get a quick reply, I cross posted.
Thanks for mentioning that link.

pmyk
10-27-2013, 05:13 AM
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
10-27-2013, 05:24 AM
Thanks p45cal for the attachment with the usage of AdvancedFilter. Appreciate your help.