COMBINEARRAYS

[RicoS]

COMBINEARRAYS combines two 2D arrays into a stacked 2D array, horizontally or vertically, allowing for a default where one array is larger than the other

=LAMBDA(array_1,array_2,defaultValue,combineHorizontally,
   LET(
      isH,combineHorizontally,
      rArr_1,ROWS(array_1),
      rArr_2,ROWS(array_2),
      cArr_1,COLUMNS(array_1),
      cArr_2,COLUMNS(array_2),
      isLargestArr1,IF(isH,rArr_1>=rArr_2,cArr_1>=cArr_2),
      dflt,IF(ISBLANK(defaultValue),IF(ISNUMBER(INDEX(IF(isLargestArr1,array_2,array_1),1,1)),0,""),defaultValue),
      arr_1,IF(isLargestArr1,array_1,IF(isH,RESIZEARRAY(array_1,rArr_2,,dflt),RESIZEARRAY(array_1,,cArr_2,dflt))),
      arr_2,IF(isLargestArr1,IF(isH,RESIZEARRAY(array_2,rArr_1,,dflt),RESIZEARRAY(array_2,,cArr_1,dflt)),array_2),
      arrTrns_1,IF(isH,TRANSPOSE(arr_1),arr_1),
      arrTrns_2,IF(isH,TRANSPOSE(arr_2),arr_2),
      rArrT_1,ROWS(arrTrns_1),
      rArrT_2,ROWS(arrTrns_2),
      cArrT,COLUMNS(arrTrns_1),
      seqR,SEQUENCE(rArrT_1+rArrT_2),
      seqC,SEQUENCE(,cArrT),
      combined,IF(seqR<=rArrT_1,INDEX(arrTrns_1,seqR,seqC),INDEX(arrTrns_2,seqR-rArrT_1,seqC)),
      return,IF(isH,TRANSPOSE(combined),combined),
   return)
)

Lambda - Last Cell and Split.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Original Array 1				Original Array 2
2	1	2	3		d	e	f
3	a	b	c		4	5	6
4	@	£	$
5
6
7	Vertical array				Horizontal array
8	1	2	3		1	2	3	d	e	f
9	a	b	c		a	b	c	4	5	6
10	@	£	$		@	£	$
11	d	e	f
12	4	5	6
13
Sheet1

Cell Formulas
Range	Formula
B8:D12	B8	=COMBINEARRAYS(B2:D4,F2:H3,,)
F8:K10	F8	=COMBINEARRAYS(B2:D4,F2:H3,,1)
Dynamic array formulas.

Utilises LAMBDA Functions:
RESIZEARRAY

 

[RicoS]

If some moderator person could switch the "defaultValue,combineHorizontally" in the function header description, I'd be eternally* grateful. I had a last minute change of heart, as I thought the default value is more likely to be used than the horizontal option, as arrays will most likely be combined vertically, but could often be of varying column sizes. Thanks!



*eternally offer expires Sunday

 

[smozgur]

If some moderator person could switch the "defaultValue,combineHorizontally" in the function header description, I'd be eternally* grateful. I had a last minute change of heart, as I thought the default value is more likely to be used than the horizontal option, as arrays will most likely be combined vertically, but could often be of varying column sizes. Thanks!

@RicoS - I edited the description.

(Could you let me know what the bottom note in your message (post#2) means? "*eternally offer expires Sunday")

 

[RicoS]

Thanks! It was just a little joke. I was offering you my eternal gratitude with "until Sunday" in the small print (so not eternal at all). Interestingly, if you were to read that comment recursively, it gets less and less funny each time until it turns out not to be funny at all.

 

[smozgur]

Ah! So the question is, until which Sunday?

I am going to remove these extra posts from this thread to keep it as a Lambda function.
Just FYI: In the future, if you need a correction in a post, a vital correction like this one, and 10 minutes editing time is over, then you can click on the Report link at the bottom of the post, and explain what's wrong with the post.