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 ) ) ) )
)
qazzzlyt's avatar
qazzzlyt
Copper Contributor
Dec 02, 2024

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.

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Dec 04, 2024

    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)

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Dec 03, 2024

    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)))))

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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 ) ) ) )
)
    • qazzzlyt's avatar
      qazzzlyt
      Copper Contributor
      Dec 08, 2024

      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.

Resources