AREPORT

[Xlambda]

AREPORT builds Array Reports PT style. !! NEW !! BYROW , BYCOL . Calls APP2H , APP2V , AFILL

=LAMBDA(ma,[lc],[h],[tc],[t],[cr],[crv],
    LET(x,APP2V(APP2V(h,APP2H(APP2H(lc,ma),tc),,-1),t,,1),y,FILTER(x,NOT(BYCOL(x,LAMBDA(a,AND(a=""))))),
      z,FILTER(y,NOT(BYROW(y,LAMBDA(a,AND(a=""))))),r,ROWS(z),c,COLUMNS(z),
      AFILL(AFILL(z,cr,r,1,c,1),crv,r,r,c,c)
    )
)

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
1	clock map of arg. sequence						h								single "vectors"
2	clockwise from 9h to 6h						A	B	C	D
3					lc		ma					tc			=AREPORT(G4#,lc)							=AREPORT(G4#,,h)
4		h(12h)			a		1	2	3	4		10			a	1	2	3	4			A	B	C	D
5	lc(9h)	⏲	tc(3h)		b		5	6	7	8		26			b	5	6	7	8			1	2	3	4
6		t(6h)			c		9	10	11	12		42			c	9	10	11	12			5	6	7	8
7					d		13	14	15	16		58			d	13	14	15	16			9	10	11	12
8	lc,h,tc,t,cr,crv				e		17	18	19	20		74			e	17	18	19	20			13	14	15	16
9	9h,12h,3h,6h,,				f		21	22	23	24		90			f	21	22	23	24			17	18	19	20
10							t					crv										21	22	23	24
11	ma: main array						66	72	78	84		300
12	lc: leading column														=AREPORT(G4#,,,tc)							=AREPORT(G4#,,,,t)
13	h: headers						cr								1	2	3	4	10			1	2	3	4
14	tc trailing column						R	TC	T						5	6	7	8	26			5	6	7	8
15	t: totals														9	10	11	12	42			9	10	11	12
16	cr: corners					all "vectors"									13	14	15	16	58			13	14	15	16
17	crv: corners value														17	18	19	20	74			17	18	19	20
18						=AREPORT(G4#,lc,h,tc,t,G14:J14,L11)									21	22	23	24	90			21	22	23	24
19	name ranges					R	A	B	C	D	TC											66	72	78	84
20	lc: E4:E9					a	1	2	3	4	10
21	h: G2:J2					b	5	6	7	8	26
22	tc: L4:L9					c	9	10	11	12	42
23	t: G11:J11					d	13	14	15	16	58
24						e	17	18	19	20	74
25						f	21	22	23	24	90
26						T	66	72	78	84	300
27
28
AREPORT post

Cell Formulas
Range	Formula
O3,F18,V12,O12,V3	O3	=FORMULATEXT(O4)
G4:J9	G4	=SEQUENCE(6,4)
O4:S9	O4	=AREPORT(G4#,lc)
V4:Y10	V4	=AREPORT(G4#,,h)
O13:S18	O13	=AREPORT(G4#,,,tc)
V13:Y19	V13	=AREPORT(G4#,,,,t)
F19:K26	F19	=AREPORT(G4#,lc,h,tc,t,G14:J14,L11)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
h	='AREPORT post'!$G$2:$J$2	V4, F19
lc	='AREPORT post'!$E$4:$E$9	O4, F19
t	='AREPORT post'!$G$11:$J$11	V13, F19
tc	='AREPORT post'!$L$4:$L$9	O13, F19

 

[Xlambda]

Hi,

I'm not sure if ths has been done / solved with LAMBDAs before.

I have two lists, and want to do a two way check (to see what is in List A, but not in List B, and what is in List B, but not in List A)

Achievable, using a combination of UNIQUE, FILTER, and COUNTIFS

AB(a,b,[k])
a,b: arrays
[k]: if omitted=>in a not in b, k=1 or <>0 => in b not in a

=LAMBDA(a,b,[k],LET(u,UNIQUE(VSTACK(a,b)),IF(k,UNIQUE(VSTACK(a,u),,1),UNIQUE(VSTACK(b,u),,1))))

Book1
	A	B	C	D	E	F	G	H	I
1
2		a				b
3		1	Cosmic	AB		1	Cosmic	AB
4		2	SpaceX	DE		5	Quantum	CG
5		3	StarBird	FG		6	Odyssey Quest	AB
6		4	FalconX	DE		4	FalconX	DE
7
8
9		k, omitted => in a not in b				k,1 => in b not in a
10		=AB(B3:D6,F3:H6)				=AB(B3:D6,F3:H6,1)
11		2	SpaceX	DE		5	Quantum	CG
12		3	StarBird	FG		6	Odyssey Quest	AB
13
14
Sheet1

Cell Formulas
Range	Formula
B10,F10	B10	=FORMULATEXT(B11)
B11:D12	B11	=AB(B3:D6,F3:H6)
F11:H12	F11	=AB(B3:D6,F3:H6,1)
Dynamic array formulas.

 

[st001]

Amazing!
I will test this, but looks a lot simpler than I thought. I learn everyday!
Thank you.