SOLVED

# Join two spill ranges to one range with two columns

Occasional Contributor

# 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 but I do not have access to O365 yet through my corporate Excel.

Any solution to this?

Thanks!

12 Replies
best response confirmed by mlylyly (Occasional Contributor)
Solution

# Re: Join two spill ranges to one range with two columns

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.

# Re: Join two spill ranges to one range with two columns

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

# Re: Join two spill ranges to one range with two columns

Thanks, I used dscheikey's version, worked like a charm!

# Re: Join two spill ranges to one range with two columns

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.

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

# Re: Join two spill ranges to one range with two columns

You could do this:

``=FILTER(A2:G4,A2:G2<>"")``

# Re: Join two spill ranges to one range with two columns

Thanks but I am looking for a solution which would work in a let() function without helper spills

# Re: Join two spill ranges to one range with two columns

Where are the "helper spills" ?

# Re: Join two spill ranges to one range with two columns

Maybe I'm not following but if I have a formula with

=LET(arrray1,array2,output) I cannot refer to ranges across the two arrays e.g., A2:G4 and A2:G2 which covers both array1 and array2

# Re: Join two spill ranges to one range with two columns

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.

# Re: Join two spill ranges to one range with two columns

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

# Re: Join two spill ranges to one range with two columns

Try this:

``=LET(dynamic,A2#:E2#,c,COLUMNS(dynamic),top,INDEX(dynamic,1,SEQUENCE(,c)),FILTER(dynamic,top<>0))``

# Re: Join two spill ranges to one range with two columns

What I was looking for, thanks a lot!