Forum Discussion

Iron Contributor

Oct 01, 2019

Solved

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

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

AML Client Finder.xlsx12 KB

BI & Data Analysis

Charting

excel

Formulas and Functions

Macros and VBA

Subodh_Tiwari_sktneer
Oct 01, 2019
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.

AML Client Finder v2.xlsm23 KB

Subodh_Tiwari_sktneer

Silver Contributor

Oct 01, 2019

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.

AML Client Finder v2.xlsm23 KB

Rajeev_Raghavan

Copper Contributor

Dec 15, 2019

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

Subodh_Tiwari_sktneer
Silver Contributor
Dec 15, 2019
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.
- Rajeev_Raghavan
  Copper Contributor
  Dec 15, 2019
  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.
  - Subodh_Tiwari_sktneer
    Silver Contributor
    Dec 15, 2019
    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
Subodh_Tiwari_sktneer
Silver Contributor
Dec 15, 2019
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