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 02, 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

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