Formula to replace VBA advanced filter

[uk747]

Hi

If I have VBA below to do an advanced filter. Was wondering how can I use a filter formula instead

I know I can use individual formulas in L2, M2, N2 etc

But I want the formula to account for values in different cells in the criteria F1:j2 which the below code does. I don't know which cells in the criteria will have value it could be 1 of them 2, 3 or even all 4

Sheets("sheet1").Range("A1") _
  .CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("sheet2") _
        .Range("F1:J2"), _
    CopyToRange:=Sheets("sheet2") _
        .Range("L1:P1"), _
    Unique:=False

 

[Fluff]

Can you post some sample data, along with what your criteria are.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[uk747]

So what I need is if I have 1 or more than 1 option in the Criteria Range the formula will pick them up which is what the Advanced Filter will do

e.g.
Criteria is Name Ben, Formula will show all Bens Rows.

	L	M	N	O	P
1​	Name	Country	Food	Quantity	Price
2​	Ben	Belgium	Yoghurt	2​	94.00​
3​	Ben	Belgium	Grape	4​	27.00​
4​	Ben	austria	beef	3​	35.00​
5​	Ben	Belgium	Yoghurt	5​	49.00​

Criteria is Name Ben, Country Belgium, Formula will show

	L	M	N	O	P
1​	Name	Country	Food	Quantity	Price
2​	Ben	Belgium	Yoghurt	2​	94.00​
3​	Ben	Belgium	Grape	4​	27.00​
4​	Ben	Belgium	Yoghurt	5​	49.00​

Criteria is Name Ben, Country Belgium and Food Yoghurt, formula will show

	L	M	N	O	P
1​	Name	Country	Food	Quantity	Price
2​	Ben	Belgium	Yoghurt	2​	94.00​
3​	Ben	Belgium	Yoghurt	5​	49.00​

So effectively whatever is in the Criteria Range it will work in the same way an advanced filter does including Blanks. If all Blank will show everything and anything in any of the criteria columns will show only those that aren't blank.


Data on Sheet1

	A	B	C	D	E	F	G	H	I
1​	Date	Name	Country	Random Data	Random Data	Random Data	Food	Quantity	Price
2​	01/02/2022​	Adam	Belgium	a	a	a	beef	4​	5.00​
3​	02/02/2022​	Ben	Belgium	b	b	b	Yoghurt	2​	94.00​
4​	03/02/2022​	Ben	Belgium	c	c	c	Grape	4​	27.00​
5​	04/02/2022​	Ben	austria	d	d	d	beef	3​	35.00​
6​	05/02/2022​	Carl	France	e	e	e	Yoghurt	6​	46.00​
7​	06/02/2022​	Carl	Denmark	f	f	f	beef	1​	1.00​
8​	07/02/2022​	Dave	China	g	g	g	Fish	8​	57.00​
9​	08/02/2022​	Dave	France	h	h	h	beef	3​	84.00​
10​	09/02/2022​	Adam	Germany	i	i	i	Zuccini	10​	56.00​
11​	10/02/2022​	Adam	Belgium	j	j	j	Zuccini	1​	88.00​
12​	11/02/2022​	Ben	Belgium	k	k	k	Yoghurt	5​	49.00​
13​	12/02/2022​	Carl	Austria	l	l	l	apple	10​	74.00​

Sheet 2 Criteria

	F	G	H	I
1​	Name	Country	Food	Quantity
2​

Sheet 2 Copy to Range

	L	M	N	O	P
1​	Name	Country	Food	Quantity	Price
2​
3​
4​
5​
6​
​
​

 

[Fluff]

Ok, how about

=FILTER(FILTER(Sheet1!B2:I30,(Sheet1!B2:B30<>"")*(IF(F2="",1,Sheet1!B2:B30=F2))*(IF(G2="",1,Sheet1!C2:C30=G2))*(IF(H2="",1,Sheet1!G2:G30=H2))*(IF(I2="",1,Sheet1!H2:H30=I2))),{1,1,0,0,0,1,1,1})

 

[uk747]

Thanks Fluff

The first part does it matter what the Sheet1!B2:B30<>"" is, as long as its a column in the Range i.e. C2:C30 or G2:G30. Even the columns which I don't need to return work i.e. Sheet1!B2:I30,(Sheet1!D2:D30<>"")

Sheet1!B2:I30,(Sheet1!B2:B30<>"")

PS the formula above works. Just wondering if its possible to incorporate the headings in case they changed like an Advanced Filter Does

I.e. if I wanted to change the Criteria Headings or Copy to Range Headings

e.g. If Criteria headings were changed from
Name Country Food Quantity to Date Country Food Quantity


Could it implemented in the formula

 

[Fluff]

The first part does it matter what the Sheet1!B2:B30<>"" is,

Nope, that's just eliminate blank rows below the data.

Just wondering if its possible to incorporate the headings in case they changed

Yup, use

=FILTER(FILTER(Sheet1!A2:I30,(Sheet1!B2:B30<>"")*(IF(F2="",1,Sheet1!B2:B30=F2))*(IF(G2="",1,Sheet1!C2:C30=G2))*(IF(H2="",1,Sheet1!G2:G30=H2))*(IF(I2="",1,Sheet1!H2:H30=I2))),COUNTIFS(L1:P1,Sheet1!A1:I1))

although the order of the headings must be the same in both sheets.

 

[uk747]

Thanks

=FILTER(FILTER(Sheet1!A2:I30,(Sheet1!B2:B30<>"")*(IF(F2="",1,Sheet1!B2:B30=F2))*(IF(G2="",1,Sheet1!C2:C30=G2))*(IF(H2="",1,Sheet1!G2:G30=H2))*(IF(I2="",1,Sheet1!H2:H30=I2))),COUNTIFS(L1:P1,Sheet1!A1:I1))

I Changed IF(F2="",1,Sheet1!B2:B30=F2 to IF(F2="",1,Sheet1!A2:A30=F2 as I tried a Date in F2

But doesnt work when I changed the Criteria Headings (F1:I1)
e.g. If Criteria headings were changed from
Name Country Food Quantity to Date Country Food Quantity

 

[Fluff]

That suggests that you dates are text & not real dates. It would be best to convert them to real dates.

 

[uk747]

Hi

The Dates are Fine

The formula doesn't reference the Headings in the Criteria

I wanted to be able to change the Headings in the Criteria F1:I1 and when I change values in F2:I2 it would pick up based on Criteria Headings

The formula you posted
=FILTER(FILTER(Sheet1!A2:I30,(Sheet1!B2:B30<>"")*(IF(F2="",1,Sheet1!B2:B30=F2))*(IF(G2="",1,Sheet1!C2:C30=G2))*(IF(H2="",1,Sheet1!G2:G30=H2))*(IF(I2="",1,Sheet1!H2:H30=I2))),COUNTIFS(L1:P1,Sheet1!A1:I1))

I had to change to below if I entered a Date in F2. I was hoping by Changing the Criteria Heading from Name to Date it would work
=FILTER(FILTER(Sheet1!A2:I30,(Sheet1!B2:B30<>"")*(IF(F2="",1,Sheet1!A2:A30=F2))*(IF(G2="",1,Sheet1!C2:C30=G2))*(IF(H2="",1,Sheet1!G2:G30=H2))*(IF(I2="",1,Sheet1!H2:H30=I2))),COUNTIFS(L1:P1,Sheet1!A1:I1))

 

[Fluff]

If you want it to work the exact same way as the advanced filter, then use advanced filter. Why re-invent the wheel?