VStack with multiple filters

[VanDookie]

Hi. I'm using this Filter VStack formula currently and it work as it should. I would like to see if I can filter out rows with a vaule of 0 in the E column as well.

=FILTER(VSTACK(Data!B4:G15,Data!B19:G35,Data!B39:G43,Data!B47:G51),VSTACK(Data!B4:B15,Data!B19:B35,Data!B39:B43,Data!B47:B51)<>0)

Any help is greatly appreciated. Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(v,VSTACK(Data!B4:G15,Data!B19:G35,Data!B39:G43,Data!B47:G51),FILTER(v,(INDEX(v,,1)<>0)*(INDEX(v,,4)<>0)))

 

[VanDookie]

=LET(v,VSTACK(Data!B4:G15,Data!B19:G35,Data!B39:G43,Data!B47:G51),FILTER(v,(INDEX(v,,1)<>0)*(INDEX(v,,4)<>0)))

Thank you. I tried this and it resulted in an n/a error.

 

[Fluff]

Do you have any #N/A errors in the data?

 

[VanDookie]

Do you have any #N/A errors in the data?

yes in the C column i believe of dataset. but not in the results

 

[Fluff]

What about col E or col B?

 

[VanDookie]

What about col E or col B?

I actually did find some n/a errors in e39:e43. these are filtered out in the original formula

 

[Fluff]

But as you now want to filter on col E, it's best to get rid of them.

 

[VanDookie]

But as you now want to filter on col E, it's best to get rid of them.

It's working. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.