Oct 01 2019 06:45 PM
Hi Team,
I have large data set of transactions, i wish to be able to show results which match a set of criteria or most of it. For example i may have 10 criteria set for the data set, but want to isolate the transactions which matches 8 or more of the criteria.
I have attached an example of set up. The goal is to be able to filter transactions to find if any trading transactions are irregular.
Thank you for your assistance.
Oct 01 2019 09:01 PM
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.
Oct 01 2019 10:58 PM
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.
Oct 01 2019 11:57 PM
SolutionIf 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.
Oct 02 2019 04:27 PM
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.
Oct 02 2019 10:14 PM - edited Oct 02 2019 10:17 PM
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:=False
But 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:=False
And 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.
Oct 02 2019 11:15 PM
Oct 02 2019 11:41 PM
Oct 02 2019 11:53 PM
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,
Oct 03 2019 12:16 AM
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...
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.
Oct 03 2019 04:14 PM
Thank you again for your help, your true asset to the Microsoft community.
Kind regards,
Oct 03 2019 09:31 PM
You're welcome again! Thanks for the feedback!
Dec 15 2019 05:13 AM
Dec 15 2019 06:56 AM
If you have more than two criteria for the same column, you may insert a helper column and filter the data on that helper column.
e.g.
In AU2 enter the below formula...
=OR(LEFT(H2,1)="6",LEFT(H2,1)="7",LEFT(H2,1)="9")
and copy it down.
And then filter the column AU for the criteria True with one of the following lines...
ActiveSheet.Range("$A$1:$AU$670").AutoFilter Field:=47, Criteria1:=True
OR
You may replace the field index number 47 with Range("AU1").Column so that it's easy for you to visualize which column is being filtered just by looking at the code.
ActiveSheet.Range("$A$1:$AU$670").AutoFilter Field:=Range("AU1").Column, Criteria1:=True
Dec 15 2019 06:59 AM
Btw 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.
Dec 15 2019 07:38 AM
Thanks for the support, actually that was a mistake because of i am new in this community.
please go through the below pic, in column i numbers are in text format.
Dec 15 2019 08:56 AM
You have placed that formula in column H and the formula is referring to the column H itself which will cause the circular reference.
If seems you are trying to filter the Material column which is column I so replace the column letter H with column letter I in the formula and place it in column H.
e.g.
In H2
=OR(LEFT(I2,1)="6",LEFT(I2,1)="7",LEFT(I2,1)="9")
Then you will need to change the filter code like this...
ActiveSheet.Range("$A$1:$AU$670").AutoFilter Field:=8, Criteria1:=True
Dec 15 2019 09:13 AM
Oct 01 2019 11:57 PM
SolutionIf 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.