Vstack/Filter Function Help

[Arts]

Hi all

I have used the VSTACK/FILTER across two sheets and this has for the most part done what I require, (Great function by the way!)

Now the issue I am having is how to use the filter function with this in main the second aspect of the filter function in what to include.

I have used the formula =VSTACK(Sheet2!B4:C18,Sheet3!B4:C18)

But this has returned me results with "0"'s.

I cannot seem to exclude the 0 with this in my result. I did see online to include FILTER with this to remove the zero but with the example they had used the VSTACK function was used holding shift (3D Reference??) allowing them to edit the range to not include 0 but I seem unable to do this when selecting the the range from two sheets.

I did try the below but this seems to return back a VALUE Error

=FILTER(VSTACK(Sheet2!B4:C18,Sheet3!B4:C18),(VSTACK(Sheet2!B4:C18,Sheet3!B4:C18)<>""

ID-01	Project 1
ID-02	Project 2
ID-03	Project 3
ID-04	Project 4
ID-05	Project 5
ID-06	Project 6
ID-07	Project 7
0​	0​
0​	0​
0​	0​
0​	0​
0​	0​
0​	0​
0​	0​
0​	0​
ID-101	Project 101
ID-102	Project 102
ID-103	Project 103
ID-104	Project 104
ID-105	Project 105
ID-106	Project 106
ID-107	Project 107
ID-108	Project 108
0​	0​
0​	0​
0​	0​
0​	0​
0​	0​
0​	0​
0​	0​

As always any help on this is appreciated.

* whist my profile says 2016 I using 365 at work

 

[Fluff]

How about

=LET(v,VSTACK(Sheet2!B4:C18,Sheet3!B4:C18),FILTER(v,INDEX(v,,1)<>""))

 

[Arts]

How about

=LET(v,VSTACK(Sheet2!B4:C18,Sheet3!B4:C18),FILTER(v,INDEX(v,,1)<>""))

Perfect, works amazingly!

I assume there is no way to edit the range any other way in the second aspect of the FILTER? (Include aspect) I only say this as to learn what you provided seems quite complex!

Was hoping for a slight variation of my earlier failed formula

 

[Fluff]

If you want it like the formula you posted it would need to be

=FILTER(VSTACK(Sheet2!B4:C18,Sheet3!B4:C18),VSTACK(Sheet2!B4:B18,Sheet3!B4:B18)<>"")

 

[Arts]

If you want it like the formula you posted it would need to be

=FILTER(VSTACK(Sheet2!B4:C18,Sheet3!B4:C18),VSTACK(Sheet2!B4:B18,Sheet3!B4:B18)<>"")

Thank you so much for this!

Another follow up (apologies) in this instance the formula you provided works as both ranges are in the same columns within separate sheets, how would I do this say if my ranges were to be in different columns when using the VSTACK (hope what I'm saying makes sense) example below

=FILTER(VSTACK(Sheet2!B4:C18,Sheet3!C4:D18) (how would I exclude the 0's in this instance using the second solution you provided)

 

[Fluff]

Use the first option, it's easier.

 

[Arts]

Use the first option, it's easier.

Haha (easier if you have Fluff like jedi excel skills!) I'll go over the first option (again) you provided and see if I can decipher, thanks once again for you help with this.

 

[Fluff]

All it does is store the vstack in a variable & then filters that based on the 1st column.

 

[Arts]

All it does is store the vstack in a variable & then filters that based on the 1st column.

Appreciate the explanation! When ever I have used the filter function I have never seen it used with LET and INDEX so assumed it complex as soon as I saw it. Will take it away, something to do over the weekend....

 

[Fluff]

Glad to help & thanks for the feedback.