Forum Discussion
Formula VSTACK/HSTACK with blank array
I have a formula =VSTACK(a,b,c,d), while length of each array is not fixed.
Now, sometimes, let's say d is blank, or N/A. I want the result to be the stack of a+b+c.
One solution is to use a IFERROR(d,CHAR(160)), then use FILTER() to exclude the dummy character.
Is there a more elegant way?
As variant
=LET( FilterRange, LAMBDA(rng,id,name, IFERROR( FILTER(rng, CHOOSECOLS(rng, id) = name ), "⚽" ) ), Combined, VSTACK( FilterRange( A1:B1000, 1, "Bird" ), FilterRange( D1:E1000, 1, "Dog" ) ), FILTER( Combined, NOT( ISNA( CHOOSECOLS(Combined, 2 ) ) ) ) )
As variant
= TOCOL( VSTACK(a,b,c,d), 3 )Try below:
=LET( arrs, {a, b, c, d}, validArrs, FILTER(arrs, NOT(ISNA(arrs))), VSTACK(validArrs) )It would be helpful if you show us few of a sample data and desired output. You may attach an Excel workbook to your post or share via OneDrive or Google-Drive.
Thank you all. See attached sample.
I want to take Birds from Zoo A and Dogs from Zoo B.
However, there is no Dog in Zoo B.
Solution A does not work. One #CALC! will stop entire VSTACK()
In solution B, I have to Filter() in separate cells first, then VSTACK() them.
It seems there is no elegant way if I have more Zoos.
As variant
=LET( FilterRange, LAMBDA(rng,id,name, IFERROR( FILTER(rng, CHOOSECOLS(rng, id) = name ), "⚽" ) ), Combined, VSTACK( FilterRange( A1:B1000, 1, "Bird" ), FilterRange( D1:E1000, 1, "Dog" ) ), FILTER( Combined, NOT( ISNA( CHOOSECOLS(Combined, 2 ) ) ) ) )Thank you all.
Seems we can't avoid something like ⚽ or CHAR(160).
LAMBDA + LET looks like the best solution because duplication is avoided.
I guess due to VSTACK grammar (array1, [array2], ...), the designer cannot find a place to accommodate a parameter to ignore error or blank value.
Thank you again.
Utilize Is_Empty parameter of FILTER() function. Then filter non error strings.
=LET(x,VSTACK(FILTER(A:B,A:A="Bird",""),FILTER(D:E,D:D="Dog","")),FILTER(x,NOT(ISERROR(CHOOSECOLS(x,2)))))Another variant.
=LET( a,VSTACK( EXPAND(FILTER(A2:B10,A2:A10="Bird",""),,2,""), EXPAND(FILTER(D2:E10,D2:D10="Dog",""),,2,"") ), b,-SUM(--(a=""))/2, c,DROP(a,b), c)
At the moment the solution I have proposed is somewhat messy but I have attempted a solution because I also have use for varying the numbers of parameters and filtering out errors.
=LAMBDA(a, b, [c], [d], LAMBDA(criterion, LET( stackϑ, VSTACK(THUNK(a), THUNK(b), THUNK(c), THUNK(d)), resultϑ, MAP( stackϑ, LAMBDA(ϑ, THUNK( FILTER(DROP(ϑ(), , 1), TAKE(ϑ(), , 1) = criterion) ) ) ), isValid, MAP(resultϑ, LAMBDA(ϑ, TYPE(ϑ()) <> 16)), EVALTHUNKARRλ(FILTER(resultϑ, isValid)) ) ) )(Table1, Table2, Table3)(filterValue)The idea is to convert the list of parameters into an array of functions. MAP accesses the functions one at a time, extracts the argument, and performs the calculation. Ideally the calculation should be performed by a function the user provides as a parameter but I haven't persuaded that not to throw an error as yet! The results are reassembled as an array of functions (thunks). Each element of the array is checked for errors (TYPE=16) and, being an array, the errors can be filtered out.
What comes next is even more obscure, but it uses a function EVALTHUNKARRλ that I have posted as a Gist on GitHub to expand the thunk array, so it can be used as a black box.
