Forum Discussion
Find rows of data which match all or majority of criteria in excel
If you have more than one AND criteria for a column, you will need to have the same column header in the criteria range. So in this case you should have two headers Transaction % in the criteria range.
I have entered some dummy value in column H on Data Set Sheet to populate the Transaction % column and added two criteria in the criteria range H2:I2 on Matching Data Sheet which automatically updates the Tracker IDs output.
I have also tweaked both the codes based on the additional Transaction % header in the criteria range.
Please refer to the attached for more details.
If you place your criteria in columns in the range A1:I1 instead of rows as shown in the Matching Data Sheet in the attached, you may use Advanced Filter to get the matching data as per the criteria you set in the range A2:I2.
Place the following code on a Standard Module like Module1:
Sub GetMatchingData()
Dim wsData As Worksheet
Dim wsOutput As Worksheet
Dim lr As Long
Dim dlr As Long
Application.ScreenUpdating = False
Set wsData = Worksheets("Data Set") 'Data Sheet
Set wsOutput = Worksheets("Matching Data") 'Result Sheet
lr = wsData.Cells(Rows.Count, "B").End(xlUp).Row
dlr = wsOutput.Cells(Rows.Count, "A").End(xlUp).Row
If dlr > 5 Then wsOutput.Range("A6:A" & dlr).Clear
wsData.Range("B3:J" & lr).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsOutput.Range("A1:I2"), CopyToRange:=wsOutput.Range("A5"), Unique:=False
Application.ScreenUpdating = True
End Sub
And then place the following code on Matching Data Sheet Module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
If Not Intersect(Target, Range("A2:I2")) Is Nothing Then
Application.EnableEvents = False
GetMatchingData
Application.EnableEvents = True
End If
Skip:
Application.EnableEvents = True
End Sub
Once you set any criteria in the range A2:I2 on Matching Data Sheet, the matching Tracker ID would be updated automatically.
Please refer to the attached for more details.
Thank you kindly for your reply, its extremely helpful.
I just have a question is it looking for just one exact match, or will it show multiple results if there is.
One other point i am wondering is if i have a criteria such transaction % between 90-100% would i just put this as column H as one formula, or would i need to insert columns? So if i want to find transactions which fall between two sets of criteria, what is the best method to insert this condition.
Thank you again for your assistance, very much appreciated.
If you have more than one AND criteria for a column, you will need to have the same column header in the criteria range. So in this case you should have two headers Transaction % in the criteria range.
I have entered some dummy value in column H on Data Set Sheet to populate the Transaction % column and added two criteria in the criteria range H2:I2 on Matching Data Sheet which automatically updates the Tracker IDs output.
I have also tweaked both the codes based on the additional Transaction % header in the criteria range.
Please refer to the attached for more details.
- ActiveSheet.Range("$A$1:$AT$670").AutoFilter Field:=8, Operator:=xlFilterValues, Criteria1:=Array("=7*", "=6*")
need to add "=9*" as third criteria, help me to find out the solutionBtw it's a good practice to start your own question. I have noticed that you have posted your question in several other questions as well.
Thank you again for your help.
I have looked at this and just have a few more quesitons hopefully you can clarify.
If i insert new columns such as "Country" as a criteria, will i need to change to VB code? Also with this example if i have a list of possible countries, can i put it in one column or need to insert many? An example if it matches one of the following countries, Australia, NZ or Thailand.
Finally similar to the above country question if I decide only say 8/10 of the criteria need to be met to return results, would this be possible?
Thank you greatly for your assistance, much appreciated.
If you add another field in the data set which is not included in the criteria range, you should only tweak the range referring to the Data Set in the code which is this, see the bold part of the following line...
wsData.Range("B3:J" & lr).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsOutput.Range("A1:J2"), CopyToRange:=wsOutput.Range("A5"), Unique:=FalseBut if the additional field say Country is also the part of the Criteria, you should add a header in the criteria range and tweak the CriteriaRange in the following line as well...
wsData.Range("B3:J" & lr).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsOutput.Range("A1:J2"), CopyToRange:=wsOutput.Range("A5"), Unique:=FalseAnd if you have any OR criteria for the same field, you must mention that criteria in the next row whereas for the AND criteria you duplicate the header in the criteria range.
e.g. if you have two OR criteria for Tracking which is A2 right now, the first criteria you should mention in A2 and another OR criteria in A3 and tweak the CriteriaRange in the code as below...
CriteriaRange:=wsOutput.Range("A1:J3")
As you can see that the criteria range also includes row3 now.
Same way after adding the Country field to the data set, tweak the CriteriaRange and Data Set Range accordingly.
Also, if you have 10 fields in the criteria range, the result set will be based on the number of criteria you mention in those 10 criteria fields. So if you mention only one criteria, the result set will be based on that criteria and it will keep on updated as you add more criteria in the criteria range.
