SOLVED

Find rows of data which match all or majority of criteria in excel

Iron Contributor

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.

17 Replies

@calof1 

 

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.

 

 

Hi@Subodh_Tiwari_sktneer 

 

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.

best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

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.

 

 

Hi@Subodh_Tiwari_sktneer 

 

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.

 

 

 

@calof1 

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.

Hi@Subodh_Tiwari_sktneer 

 

Thank you again for your help, it is immensly appreciated.

Kind regards,

@calof1 

You're welcome! Glad I could help.

 

Regard,

Subodh

HIi@Subodh_Tiwari_sktneer 

 

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,

@calof1 

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...

  1. 0.4<= Transaction% <=0.6 and the country is AUS
  2. 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.

 

 

Hi@Subodh_Tiwari_sktneer 

 

Thank you again for your help, your true asset to the Microsoft community.

 

Kind regards,

@calof1 

You're welcome again! Thanks for the feedback!

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

@Rajeev_Raghavan 

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

 

@Rajeev_Raghavan 

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.

@Subodh_Tiwari_sktneer 

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.

 

Capture.PNG

@Rajeev_Raghavan 

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

Done,

Thanks for the quick support :)
1 best response

Accepted Solutions
best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

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.

 

 

View solution in original post