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.
Hope you are well.
I have tried to include Country in Column J, as a criteria and in the data set. This is an OR criteria, so i added values to J2 & J3. I have changed the code to include row 3 and column k also. From my view it should show tracker 1 & 2, however it appears to be blank.
If possible can you please highlight which step is missing.
Many thanks,
I forgot to tell you that you also need to tweak the code on Matching Data Sheet Module which should be like this after changing the criteria range on Matching Data Sheet.
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:K3")) Is Nothing Then
Application.EnableEvents = False
GetMatchingData
Application.EnableEvents = True
End If
Skip:
Application.EnableEvents = True
End Sub
So now the result data set would return the Tracker IDs for two conditions...
- 0.4<= Transaction% <=0.6 and the country is AUS
- OR the country is UK without having any criteria for Transaction%
As a result, it will return Tracker IDs 1 and 7.
If you want to apply the Transaction% criteria for UK also, you should mention the Transaction% criteria in H3 and I3 also.
You're welcome again! Thanks for the feedback!
Thank you again for your help, your true asset to the Microsoft community.
Kind regards,