VSTACK in Excel used to combine two sets of data resulting in calc error if one set is empty-CodePudding

I have the following function to combine two sets of data.

=FILTER(VSTACK(FILTER(M3:Q50000,M3:M50000<>""),FILTER(S3:W50000,S3:S50000<>"")),{1,1,1,0,1})

If there is valid data, it works fine, however if there is no data in one of the datasets it results in a calc error. I have tried protecting against the errors using the following:

=FILTER(VSTACK(IFERROR(FILTER(M3:Q50000,M3:M50000<>""),""),IFERROR(FILTER(S3:W50000,S3:S50000<>""),"")),{1,1,1,0,1})

However this gives a row with N/A in each cell and then follows on with the data defined from S3:W onwards.

Is there a way to protect the FILTERS so it can continue to work should either of the datasets are empty?

CodePudding user response：

You can handle the missing data with something like this:

=LET(x,FILTER(M3:Q50000,M3:M50000<>"",""),y, FILTER(S3:W50000,S3:S50000<>"",""),z,VSTACK(x,y),IFERROR(CHOOSECOLS(FILTER(z,(INDEX(z,0,1)<>"")),1,2,3,5),"No data"))

using CHOOSECOLS rather than another FILTER function.

CodePudding user response：

Give a try on-

=LET(x,FILTER(A1:C12,A1:A12<>"",""),y,FILTER(E1:G12,E1:E12<>"",""),IF(AND(x=""),IF(y="","",y),IF(AND(y<>""),VSTACK(x,y),x)))