Forum Discussion

mlylyly's avatar
mlylyly
Copper Contributor
Nov 14, 2022
Solved

Join two spill ranges to one range with two columns

Hi community   I would like to join two spill ranges into one range with two columns but cannot figure out how to do it. What I would like is the new range below. I believe it is what HSTACK() does...
excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Nov 14, 2022

    mlylyly 

    For this task you can also use the CHOOSE() function in older Excel versions.

    =CHOOSE({1,2},E4:E6,G4:G6)

    In cases where your Range 1 = E4:E6 and Range 2 = G4:G6.

    In older versions of Excel you have to finish this with CTRL + SHIFT + ENTER as an array formula.

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Nov 14, 2022

mlylyly 

If you're looking to join two spills, then you most likely have 365 but without HSTACK (Possibly on Semi-Annual channel).  This might work in your version.

=IF({1,0},A1#,C1#)
  • mlylyly's avatar
    mlylyly
    Copper Contributor
    Nov 18, 2022

    Patrick2788 

     

    dscheikey 

     

    Both of your solutions work but I have to expand it to joining to arrays with multiple columns, do you have a solution for that? 

    I tried a simple expansion of both of your formulas but either seem to work.

    Thanks in advance!

    P.s., yes I have 365 but without HSTACK due to semi-annual channel, you're right

     

    • dscheikey's avatar
      dscheikey
      Bronze Contributor
      Nov 18, 2022

      mlylyly 

      If your real data is also as simple as the example, I would do it that way:

      =LET(array1,A2#,array2,E2#,INDEX(array1:array2,{1;2;3},{1,2,3,5,6,7}))

       

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Nov 18, 2022

      mlylyly 

      Somewhat less elegant but playing with indices will work.

      = LET(
      k, SEQUENCE(1,6),
      h, SEQUENCE(3),
      IF(k<4, array1#, INDEX(array2#,h,k-3))
   )

      Of course, provided you have Lambda, you could always provide some window dressing

      WorksheetFormula
= HStackλ(array1#, array2#)

HStackλ 
= LAMBDA(a₁, a₂,
     LET(
        k, SEQUENCE(1, COLUMNS(a₁) + COLUMNS(a₂)),
        h, SEQUENCE(MAX(ROWS(a₁), ROWS(a₂))),
        IF(k < 4, a₁, INDEX(a₂, h, k - 3))
     )
  )

      Then again, you could make a case to corporate IT to put you on to the Insider beta channel so that you could preview new functionality on behalf of the company.

       

      • mlylyly's avatar
        mlylyly
        Copper Contributor
        Nov 18, 2022
        What I was looking for, thanks a lot!
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Nov 18, 2022

      mlylyly 

      You could do this:

      =FILTER(A2:G4,A2:G2<>"")
      • mlylyly's avatar
        mlylyly
        Copper Contributor
        Nov 18, 2022
        Thanks but I am looking for a solution which would work in a let() function without helper spills

Resources