Forum Discussion

qazzzlyt's avatar
qazzzlyt
Copper Contributor
Nov 30, 2024
Solved

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 IFERRO...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 02, 2024

    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 ) ) ) )
)
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 03, 2024

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.

 

 

Resources