APUZZLE

[Xlambda]

APUZZLE replaces selected areas of an array "a" with other array "b" values , from left to right , top to bottom. !! NEW !! MAKEARRAY , SCAN . Calls AFLAT

=LAMBDA(a,b,r1w,r2w,c1l,c2l,
    LET(fb,AFLAT(b,1),r,ROWS(a),c,COLUMNS(a),x,MEDIAN(1,IF(r1w,r1w,1),r),y,MEDIAN(1,IF(r2w,r2w,r),r),z,MEDIAN(1,IF(c1l,c1l,1),c),w,MEDIAN(1,IF(c2l,c2l,c),c),
      m,MAKEARRAY(r,c,LAMBDA(r,c,IF(x>y,(r>=x)+(r<=y),(r>=x)*(r<=y))*IF(z>w,(c>=z)+(c<=w),(c>=z)*(c<=w)))),
      s,IFERROR(INDEX(fb,SCAN(0,m,LAMBDA(v,a,v+a))),""),
      IF(m,s,IF(a="","",a))
    )
)

LAMBDA 1.1.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	APUZZLE replaces areas of an array "a" with other array "b" values , from left to right , top to bottom
2	r1w,r2w,c1l,c2l are required arguments that help us select only certain areas of source array to be replaced. Data outside area selected will remain unchanged
3	r1w,r2w,c1l,c2l, functionality
4	a.) if arg. are ignored or out of range formula will asign this value r1w=1,r2w=rows(a),c2l=1,c2l=clms(a), this means the whole array will be replaced
5	Note:If an argument is ignored we still have to write the commas, for omitted arguments we don't
6	Default value for an "ignored" required argument is a blank (a blank is a 0 and a null string on the same time)
7	I have used"can be ignored"arguments, and not omitted, because on most case scenarios, we do have to select some values																						b
8	b.) if values r1w,r2w not ignored, formula will select all the rows (rw) that follow this condition: rw>=r1w and rw<=r2w																							E	F
9	c.) if values c1l,c2l not ignored, formula will select all the clms (cl) that follow this condition: cl>=c1l and cl<=c2l																						G	H
10																							J	K	L
11	Examples of r1w,r2w,c1l,c2l various scenarios:
12
13		2,4,2,4 (arguments sequence)							2,4,4,1							5,2,5,2							5,2,2,4
14	a		>=2	cl	<=4				<=1	cl		>=4					<=2	cl		>=5				>=2	cl	<=4
15		1	2	3	4	5			1	2	3	4	5			1	2	3	4	5			1	2	3	4	5
16	>=2	6	7	8	9	10		>=2	6	7	8	9	10		<=2	6	7	8	9	10		<=2	6	7	8	9	10
17	rw	11	12	13	14	15		rw	11	12	13	14	15		rw	11	12	13	14	15		rw	11	12	13	14	15
18	<=4	16	17	18	19	20		<=4	16	17	18	19	20			16	17	18	19	20			16	17	18	19	20
19		21	22	23	24	25			21	22	23	24	25		>=5	21	22	23	24	25		>=5	21	22	23	24	25
20		26	27	28	29	30			26	27	28	29	30			26	27	28	29	30			26	27	28	29	30
21
22		=APUZZLE(B15#,$W$8:$Y$10,2,4,2,4)							=APUZZLE(I15:M20,$W$8:$Y$10,2,4,4,1)							=APUZZLE(P15:T20,$W$8:$Y$10,5,2,5,2)							=APUZZLE(W15:AA20,$W$8:$Y$10,5,2,2,4)
23		1	2	3	4	5			1	2	3	4	5				E	3	4	F			1		E	F	5
24		6		E	F	10				7	8	E	F			G	H	8	9				6	G	H		10
25		11	G	H		15			G	12	13	H				11	12	13	14	15			11	12	13	14	15
26		16	J	K	L	20			J	17	18	K	L			16	17	18	19	20			16	17	18	19	20
27		21	22	23	24	25			21	22	23	24	25			J	K	23	24	L			21	J	K	L	25
28		26	27	28	29	30			26	27	28	29	30					28	29				26				30
29
30		b		all rows, clms 2,4: ,,2,4						all clms rws 6,1: 6,1,,						,3,3,						only corners: 6,1,5,1
31		A		=APUZZLE(B15#,B31:B48,,,2,4)						=APUZZLE(B15#,B31:B48,6,1,,)						=APUZZLE(B15#,B31:B48,,3,3,)						=APUZZLE(B15#,B31:B48,6,1,5,1)
32		B		1	A	B	C	5		A	B	C	D	E		1	2	A	B	C		A	2	3	4	B
33		C		6	D	E	F	10		6	7	8	9	10		6	7	D	E	F		6	7	8	9	10
34		D		11	G	H	I	15		11	12	13	14	15		11	12	G	H	I		11	12	13	14	15
35		E		16	J	K	L	20		16	17	18	19	20		16	17	18	19	20		16	17	18	19	20
36		F		21	M	N	O	25		21	22	23	24	25		21	22	23	24	25		21	22	23	24	25
37		G		26	P	Q	R	30		F	G	H	I	J		26	27	28	29	30		C	27	28	29	D
38		H
39		I		more arrays, random areas, nested formula needed
40		J		b1						=APUZZLE(APUZZLE(B15#,D41:H41,,2,4,),D43:F45,4,,,2)
41		K		A	B	C	D	E		1	2	3	A	B
42		L		b2						6	7	8	C	D				resize same array , 6x5 to a 3x10
43		M		a	b	c				11	12	13	14	15				=APUZZLE(REPT("",SEQUENCE(3,10)),B15#,,,,)
44		N		d	e	f				a	b	18	19	20				1	2	3	4	5	6	7	8	9	10
45		O		g	h	i				c	d	23	24	25				11	12	13	14	15	16	17	18	19	20
46		P								e	f	28	29	30				21	22	23	24	25	26	27	28	29	30
47		Q
APUZZLE post

Cell Formulas
Range	Formula
W8	W8	=""
B15:F20	B15	=SEQUENCE(6,5)
B22,I22,P22,W22,R43,J40,V31,P31,J31,D31	B22	=FORMULATEXT(B23)
B23:F28	B23	=APUZZLE(B15#,$W$8:$Y$10,2,4,2,4)
I23:M28	I23	=APUZZLE(I15:M20,$W$8:$Y$10,2,4,4,1)
P23:T28	P23	=APUZZLE(P15:T20,$W$8:$Y$10,5,2,5,2)
W23:AA28	W23	=APUZZLE(W15:AA20,$W$8:$Y$10,5,2,2,4)
D32:H37	D32	=APUZZLE(B15#,B31:B48,,,2,4)
J32:N37	J32	=APUZZLE(B15#,B31:B48,6,1,,)
P32:T37	P32	=APUZZLE(B15#,B31:B48,,3,3,)
V32:Z37	V32	=APUZZLE(B15#,B31:B48,6,1,5,1)
J41:N46	J41	=APUZZLE(APUZZLE(B15#,D41:H41,,2,4,),D43:F45,4,,,2)
R44:AA46	R44	=APUZZLE(REPT("",SEQUENCE(3,10)),B15#,,,,)
Dynamic array formulas.