Nov 14 2022 08:01 AM
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!
Nov 14 2022 08:57 AM
SolutionFor 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.
Nov 14 2022 11:43 AM
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#)
Nov 16 2022 10:18 AM
Nov 18 2022 04:40 AM - edited Nov 18 2022 04:42 AM
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
Nov 18 2022 05:52 AM
Nov 18 2022 06:02 AM
Nov 18 2022 06:07 AM
Nov 18 2022 06:18 AM
Nov 18 2022 06:30 AM
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.
Nov 18 2022 06:34 AM
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}))
Nov 18 2022 07:07 AM
Try this:
=LET(dynamic,A2#:E2#,c,COLUMNS(dynamic),top,INDEX(dynamic,1,SEQUENCE(,c)),FILTER(dynamic,top<>0))
Nov 18 2022 07:08 AM
Nov 14 2022 08:57 AM
SolutionFor 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.