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.
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.
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.
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:=TrueOR
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
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.
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,
