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 solution
