AOVERLAP

[Xlambda]

AOVERLAP Array Overlap, overlapping 2 arrays using a row/column coordinate system. !! NEW !! MAKEARRAY

=LAMBDA(ar,br,[r],[c],
    LET(a,IF(ar="","",ar),b,IF(br="","",br),x,ROWS(a),y,ROWS(b),z,COLUMNS(a),w,COLUMNS(b),
       s,MAX(({1;1;0;0}=--(r<=0))*({1;0;1;0}=--(c<=0))*SEQUENCE(4)),
       k,MAKEARRAY(IF(s<=2,MAX(y,x-r),MAX(y+r,x)),IF(OR(s=2,s=4),MAX(w+c,z),MAX(z-c,w)),LAMBDA(m,n,SWITCH(s,
         1,IFS(AND(n<=w,m<=y),INDEX(b,m,n),AND(n>-c,m>-r),INDEX(a,m+r,n+c)),
         2,IFS(AND(n>c,n<=(c+w),m<=y),INDEX(b,m,n-c),m>-r,INDEX(a,m+r,n)),
         3,IFS(AND(m>r,m<=(r+y),n<=w),INDEX(b,m-r,n),n>-c,INDEX(a,m,n+c)),
         4,IFS(AND(m>r,m<=(r+y),n>c,n<=(c+w)),INDEX(b,m-r,n-c),TRUE,INDEX(a,m,n))))),
      IFERROR(k,"")
    )
)

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	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1	ar							r>=0,c>=0
2	1	2	3	4	5	6																													c,3	➡											c,6	➡
3	2	3	4	5	6	7		r,,c,,	➡								c,1	➡								c,2	➡						1	2	3	4	5	6				1	2	3	4	5	6
4	3	4	5	6	7	8		⬇	A	B	C	D	5	6			1	2	3	4	5	6			1	2	3	4	5	6			2	3	4	5	6	7				2	3	4	5	6	7
5	4	5	6	7		9			E	F		H	6	7			2	3	4	5	6	7		r,2	2	3	4	5	6	7		r,3	3	4	5	6	7	8				3	4	5	6	7	8
6	5	6	7	8	9	10			I	J	K	L	7	8		r,3	3	4	5	6	7	8		⬇	3	4	A	B	C	D		⬇	4	5	6	A	B	C	D			4	5	6	7		9
7	br								4	5	6	7		9		⬇	4	A	B	C	D	9			4	5	E	F		H			5	6	7	E	F		H		r,5	5	6	7	8	9	10
8	A	B	C	D					5	6	7	8	9	10			5	E	F		H	10			5	6	I	J	K	L						I	J	K	L		⬇							A	B	C	D
9	E	F		H					=AOVERLAP(A2:F6,A8:D10)									I	J	K	L				=AOVERLAP(A2:F6,A8:D10,2,2)								=AOVERLAP(A2:F6,A8:D10,3,3)															E	F		H
10	I	J	K	L													=AOVERLAP(A2:F6,A8:D10,3,1)																															I	J	K	L
11																																										=AOVERLAP(A2:F6,A8:D10,5,6)
12	r>=0,c<0
13																						⬅	c,-1										⬅	c,-2											⬅	c,-4
14		r,		⬅	c,-2								⬅	c,-1									1	2	3	4	5	6						1	2	3	4	5	6							1	2	3	4	5	6
15		⬇	A	B	C	D	3	4	5	6		r,1		1	2	3	4	5	6		r,2		2	3	4	5	6	7						2	3	4	5	6	7							2	3	4	5	6	7
16			E	F		H	4	5	6	7		⬇	A	B	C	D	5	6	7		⬇	A	B	C	D	6	7	8			r,3			3	4	5	6	7	8							3	4	5	6	7	8
17			I	J	K	L	5	6	7	8			E	F		H	6	7	8			E	F		H	7		9			⬇	A	B	C	D	6	7		9							4	5	6	7		9
18					4	5	6	7		9			I	J	K	L	7		9			I	J	K	L	8	9	10				E	F		H	7	8	9	10		r,5					5	6	7	8	9	10
19					5	6	7	8	9	10				5	6	7	8	9	10			=AOVERLAP(A2:F6,A8:D10,2,-1)										I	J	K	L						⬇	A	B	C	D
20			=AOVERLAP(A2:F6,A8:D10,,-2)										=AOVERLAP(A2:F6,A8:D10,1,-1)																			=AOVERLAP(A2:F6,A8:D10,3,-2)										E	F		H
21																																										I	J	K	L
22																																										=AOVERLAP(A2:F6,A8:D10,5,-4)
23	r<0,c<=0
24																							⬅	c,-4												⬅	c,-5
25		⬅	c,,								⬅	c,-1								A	B	C	D									A	B	C	D
26		⬆	A	B	C	D				⬆	A	B	C	D						E	F		H									E	F		H
27		r,-1	E	F		H	5	6		r,-1	E	F		H	4	5	6		⬆	I	J	K	L									I	J	K	L
28			I	J	K	L	6	7			I	J	K	L	5	6	7		r,-3					1	2	3	4	5	6		⬆
29			3	4	5	6	7	8				3	4	5	6	7	8							2	3	4	5	6	7		r,-4						1	2	3	4	5	6
30			4	5	6	7		9				4	5	6	7		9							3	4	5	6	7	8								2	3	4	5	6	7
31			5	6	7	8	9	10				5	6	7	8	9	10							4	5	6	7		9								3	4	5	6	7	8
32			=AOVERLAP(A2:F6,A8:D10,-1)								=AOVERLAP(A2:F6,A8:D10,-1,-1)													5	6	7	8	9	10								4	5	6	7		9
33																				=AOVERLAP(A2:F6,A8:D10,-3,-4)																	5	6	7	8	9	10
34																																=AOVERLAP(A2:F6,A8:D10,-4,-5)
35	r<=0,c>0
36											c,1	➡								c,2	➡								c,3	➡											c,6	➡
37		⬆	c,1	➡						⬆		A	B	C	D			⬆			A	B	C	D						A	B	C	D									A	B	C	D
38		r,,	1	A	B	C	D	6		r,-1	1	E	F		H	6		r,-1	1	2	E	F		H		⬆				E	F		H									E	F		H
39			2	E	F		H	7			2	I	J	K	L	7			2	3	I	J	K	L		r,-2	1	2	3	I	J	K	L		⬆							I	J	K	L
40			3	I	J	K	L	8			3	4	5	6	7	8			3	4	5	6	7	8			2	3	4	5	6	7			r,-3	1	2	3	4	5	6
41			4	5	6	7		9			4	5	6	7		9			4	5	6	7		9			3	4	5	6	7	8				2	3	4	5	6	7
42			5	6	7	8	9	10			5	6	7	8	9	10			5	6	7	8	9	10			4	5	6	7		9				3	4	5	6	7	8
43			=AOVERLAP(A2:F6,A8:D10,,1)								=AOVERLAP(A2:F6,A8:D10,-1,1)								=AOVERLAP(A2:F6,A8:D10,-1,2)								5	6	7	8	9	10				4	5	6	7		9
44																											=AOVERLAP(A2:F6,A8:D10,-2,3)									5	6	7	8	9	10
45																																				=AOVERLAP(A2:F6,A8:D10,-3,6)
AOVERLAP post 1

Cell Formulas
Range	Formula
AG3:AM8	AG3	=AOVERLAP(A2:F6,A8:D10,3,3)
AP3:AY10	AP3	=AOVERLAP(A2:F6,A8:D10,5,6)
I4:N8	I4	=AOVERLAP(A2:F6,A8:D10)
Q4:V9	Q4	=AOVERLAP(A2:F6,A8:D10,3,1)
Y4:AD8	Y4	=AOVERLAP(A2:F6,A8:D10,2,2)
I9,C43,M20,C20,V19,Y9	I9	=FORMULATEXT(I4)
AG9,S43,K43,K32,C32,AF20,Q10	AG9	=FORMULATEXT(AG3)
AP11,AJ45,T33,AP22	AP11	=FORMULATEXT(AP3)
V14:AB18	V14	=AOVERLAP(A2:F6,A8:D10,2,-1)
AF14:AM19	AF14	=AOVERLAP(A2:F6,A8:D10,3,-2)
AP14:AY21	AP14	=AOVERLAP(A2:F6,A8:D10,5,-4)
C15:J19	C15	=AOVERLAP(A2:F6,A8:D10,,-2)
M15:S19	M15	=AOVERLAP(A2:F6,A8:D10,1,-1)
T25:AC32	T25	=AOVERLAP(A2:F6,A8:D10,-3,-4)
AF25:AP33	AF25	=AOVERLAP(A2:F6,A8:D10,-4,-5)
C26:H31	C26	=AOVERLAP(A2:F6,A8:D10,-1)
K26:Q31	K26	=AOVERLAP(A2:F6,A8:D10,-1,-1)
AF34	AF34	=FORMULATEXT(AF25)
K37:P42	K37	=AOVERLAP(A2:F6,A8:D10,-1,1)
S37:X42	S37	=AOVERLAP(A2:F6,A8:D10,-1,2)
AA37:AG43	AA37	=AOVERLAP(A2:F6,A8:D10,-2,3)
AJ37:AS44	AJ37	=AOVERLAP(A2:F6,A8:D10,-3,6)
C38:H42	C38	=AOVERLAP(A2:F6,A8:D10,,1)
AA44	AA44	=FORMULATEXT(AA37)
Dynamic array formulas.

 

[Xlambda]

Overlapping alignment. Other functions equivalents on minisheet APP2H, APP2V

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	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1	ar							Inner alignment (no equivalent functions)
2	1	2	3	4	5	6
3	2	3	4	5	6	7		=AOVERLAP(ar,br)							=AOVERLAP(ar,br,ROWS(ar)-ROWS(br))																	=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),COLUMNS(ar)-COLUMNS(br))
4	3	4	5	6	7	8		A	B	C	D	5	6			1	2	3	4	5	6				1	2	A	B	C	D				1	2	3	4	5	6
5	4	5	6	7		9		E	F		H	6	7			2	3	4	5	6	7				2	3	E	F		H				2	3	4	5	6	7
6	5	6	7	8	9	10		I	J	K	L	7	8			A	B	C	D	7	8				3	4	I	J	K	L				3	4	A	B	C	D
7	br							4	5	6	7		9			E	F		H		9				4	5	6	7		9				4	5	E	F		H
8	A	B	C	D				5	6	7	8	9	10			I	J	K	L	9	10				5	6	7	8	9	10				5	6	I	J	K	L		other functions
9	E	F		H																				=AOVERLAP(ar,br,,COLUMNS(ar)-COLUMNS(br))																	APP2H
10	I	J	K	L																																					APP2V
11
12		Horizontal alignment
13		=AOVERLAP(ar,br,,-COLUMNS(br))										=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),-COLUMNS(br))												=AOVERLAP(ar,br,,COLUMNS(ar))											=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),COLUMNS(ar))
14		A	B	C	D	1	2	3	4	5	6						1	2	3	4	5	6		1	2	3	4	5	6	A	B	C	D		1	2	3	4	5	6
15		E	F		H	2	3	4	5	6	7						2	3	4	5	6	7		2	3	4	5	6	7	E	F		H		2	3	4	5	6	7
16		I	J	K	L	3	4	5	6	7	8		A	B	C	D	3	4	5	6	7	8		3	4	5	6	7	8	I	J	K	L		3	4	5	6	7	8	A	B	C	D
17						4	5	6	7		9		E	F		H	4	5	6	7		9		4	5	6	7		9						4	5	6	7		9	E	F		H
18						5	6	7	8	9	10		I	J	K	L	5	6	7	8	9	10		5	6	7	8	9	10						5	6	7	8	9	10	I	J	K	L
19
20		Horizontal alignment equivalent functions
21		=APP2H(br,ar)											=APP2H(br,ar,,ROWS(br)-ROWS(ar))											=APP2H(ar,br)											=APP2H(ar,br,,ROWS(ar)-ROWS(br))
22		A	B	C	D	1	2	3	4	5	6						1	2	3	4	5	6		1	2	3	4	5	6	A	B	C	D		1	2	3	4	5	6
23		E	F		H	2	3	4	5	6	7						2	3	4	5	6	7		2	3	4	5	6	7	E	F		H		2	3	4	5	6	7
24		I	J	K	L	3	4	5	6	7	8		A	B	C	D	3	4	5	6	7	8		3	4	5	6	7	8	I	J	K	L		3	4	5	6	7	8	A	B	C	D
25						4	5	6	7		9		E	F		H	4	5	6	7		9		4	5	6	7		9						4	5	6	7		9	E	F		H
26						5	6	7	8	9	10		I	J	K	L	5	6	7	8	9	10		5	6	7	8	9	10						5	6	7	8	9	10	I	J	K	L
27
28		Vertical alignment
29		=AOVERLAP(ar,br,-ROWS(br))								=AOVERLAP(ar,br,-ROWS(br),COLUMNS(ar)-COLUMNS(br))													=AOVERLAP(ar,br,ROWS(ar))								=AOVERLAP(ar,br,ROWS(ar),COLUMNS(ar)-COLUMNS(br))
30		A	B	C	D									A	B	C	D						1	2	3	4	5	6				1	2	3	4	5	6
31		E	F		H									E	F		H						2	3	4	5	6	7				2	3	4	5	6	7
32		I	J	K	L									I	J	K	L						3	4	5	6	7	8				3	4	5	6	7	8
33		1	2	3	4	5	6					1	2	3	4	5	6						4	5	6	7		9				4	5	6	7		9
34		2	3	4	5	6	7					2	3	4	5	6	7						5	6	7	8	9	10				5	6	7	8	9	10
35		3	4	5	6	7	8					3	4	5	6	7	8						A	B	C	D								A	B	C	D
36		4	5	6	7		9					4	5	6	7		9						E	F		H								E	F		H
37		5	6	7	8	9	10					5	6	7	8	9	10						I	J	K	L								I	J	K	L
38
39		Vertical alignment equivalent functions
40		=APP2V(br,ar)										=APP2V(br,ar,,COLUMNS(br)-COLUMNS(ar))											=APP2V(ar,br)									=APP2V(ar,br,,COLUMNS(ar)-COLUMNS(br))
41		A	B	C	D									A	B	C	D						1	2	3	4	5	6				1	2	3	4	5	6
42		E	F		H									E	F		H						2	3	4	5	6	7				2	3	4	5	6	7
43		I	J	K	L									I	J	K	L						3	4	5	6	7	8				3	4	5	6	7	8
44		1	2	3	4	5	6					1	2	3	4	5	6						4	5	6	7		9				4	5	6	7		9
45		2	3	4	5	6	7					2	3	4	5	6	7						5	6	7	8	9	10				5	6	7	8	9	10
46		3	4	5	6	7	8					3	4	5	6	7	8						A	B	C	D								A	B	C	D
47		4	5	6	7		9					4	5	6	7		9						E	F		H								E	F		H
48		5	6	7	8	9	10					5	6	7	8	9	10						I	J	K	L								I	J	K	L
49
AOVERLAP post 2

Cell Formulas
Range	Formula
H3,AF40,W40,L40,B40,W29,B29,AI13,X13,B13	H3	=FORMULATEXT(H4)
O3,AE29,L13	O3	=FORMULATEXT(P4)
AF3,J29	AF3	=FORMULATEXT(AH4)
H4:M8	H4	=AOVERLAP(ar,br)
P4:U8	P4	=AOVERLAP(ar,br,ROWS(ar)-ROWS(br))
Y4:AD8	Y4	=AOVERLAP(ar,br,,COLUMNS(ar)-COLUMNS(br))
AH4:AM8	AH4	=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),COLUMNS(ar)-COLUMNS(br))
X9	X9	=FORMULATEXT(Y4)
B14:K18	B14	=AOVERLAP(ar,br,,-COLUMNS(br))
M14:V18	M14	=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),-COLUMNS(br))
X14:AG18	X14	=AOVERLAP(ar,br,,COLUMNS(ar))
AI14:AR18	AI14	=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),COLUMNS(ar))
B21,AI21,X21,M21	B21	=FORMULATEXT((B22))
B22:K26	B22	=APP2H(br,ar)
M22:V26	M22	=APP2H(br,ar,,ROWS(br)-ROWS(ar))
X22:AG26	X22	=APP2H(ar,br)
AI22:AR26	AI22	=APP2H(ar,br,,ROWS(ar)-ROWS(br))
B30:G37	B30	=AOVERLAP(ar,br,-ROWS(br))
L30:Q37	L30	=AOVERLAP(ar,br,-ROWS(br),COLUMNS(ar)-COLUMNS(br))
W30:AB37	W30	=AOVERLAP(ar,br,ROWS(ar))
AF30:AK37	AF30	=AOVERLAP(ar,br,ROWS(ar),COLUMNS(ar)-COLUMNS(br))
B41:G48	B41	=APP2V(br,ar)
L41:Q48	L41	=APP2V(br,ar,,COLUMNS(br)-COLUMNS(ar))
W41:AB48	W41	=APP2V(ar,br)
AF41:AK48	AF41	=APP2V(ar,br,,COLUMNS(ar)-COLUMNS(br))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
ar	='AOVERLAP post 2'!$A$2:$F$6	AI22, AI14, AH4, AF41, AF30, Y4, X22, X14, W41, W30, P4, M22, M14, L41, L30, H4, B41, B30, B22, B14
br	='AOVERLAP post 2'!$A$8:$D$10	AI22, AI14, AH4, AF41, AF30, Y4, X22, X14, W41, W30, P4, M22, M14, L41, L30, H4, B41, B30, B22, B14