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]

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
1			h									double "vectors"
2			A	B	C	D
3	lc		ma					tc				=AREPORT(C4#,lc,h)							=AREPORT(C4#,lc,,tc)							=AREPORT(C4#,lc,,,t)
4	a		1	2	3	4		10					A	B	C	D			a	1	2	3	4	10		a	1	2	3	4
5	b		5	6	7	8		26				a	1	2	3	4			b	5	6	7	8	26		b	5	6	7	8
6	c		9	10	11	12		42				b	5	6	7	8			c	9	10	11	12	42		c	9	10	11	12
7	d		13	14	15	16		58				c	9	10	11	12			d	13	14	15	16	58		d	13	14	15	16
8	e		17	18	19	20		74				d	13	14	15	16			e	17	18	19	20	74		e	17	18	19	20
9	f		21	22	23	24		90				e	17	18	19	20			f	21	22	23	24	90		f	21	22	23	24
10			t					crv				f	21	22	23	24											66	72	78	84
11			66	72	78	84		300
12												=AREPORT(C4#,,h,tc)							=AREPORT(C4#,,h,,t)							=AREPORT(C4#,,,tc,t)
13			cr									A	B	C	D				A	B	C	D				1	2	3	4	10
14			R	TC	T							1	2	3	4	10			1	2	3	4				5	6	7	8	26
15												5	6	7	8	26			5	6	7	8				9	10	11	12	42
16												9	10	11	12	42			9	10	11	12				13	14	15	16	58
17												13	14	15	16	58			13	14	15	16				17	18	19	20	74
18												17	18	19	20	74			17	18	19	20				21	22	23	24	90
19												21	22	23	24	90			21	22	23	24				66	72	78	84
20																			66	72	78	84
21
22			triple "vectors"
23
24			=AREPORT(C4#,lc,h,tc)								=AREPORT(C4#,,h,tc,t)							=AREPORT(C4#,lc,,tc,t)								=AREPORT(C4#,lc,h,,t)
25				A	B	C	D				A	B	C	D				a	1	2	3	4	10				A	B	C	D
26			a	1	2	3	4	10			1	2	3	4	10			b	5	6	7	8	26			a	1	2	3	4
27			b	5	6	7	8	26			5	6	7	8	26			c	9	10	11	12	42			b	5	6	7	8
28			c	9	10	11	12	42			9	10	11	12	42			d	13	14	15	16	58			c	9	10	11	12
29			d	13	14	15	16	58			13	14	15	16	58			e	17	18	19	20	74			d	13	14	15	16
30			e	17	18	19	20	74			17	18	19	20	74			f	21	22	23	24	90			e	17	18	19	20
31			f	21	22	23	24	90			21	22	23	24	90				66	72	78	84				f	21	22	23	24
32											66	72	78	84													66	72	78	84
33
34
AREPORT post 2

Cell Formulas
Range	Formula
L3,Z24,R24,K24,C24,Z12,S12,L12,Z3,S3	L3	=FORMULATEXT(L4)
C4:F9	C4	=SEQUENCE(6,4)
L4:P10	L4	=AREPORT(C4#,lc,h)
S4:X9	S4	=AREPORT(C4#,lc,,tc)
Z4:AD10	Z4	=AREPORT(C4#,lc,,,t)
L13:P19	L13	=AREPORT(C4#,,h,tc)
S13:V20	S13	=AREPORT(C4#,,h,,t)
Z13:AD19	Z13	=AREPORT(C4#,,,tc,t)
C25:H31	C25	=AREPORT(C4#,lc,h,tc)
K25:O32	K25	=AREPORT(C4#,,h,tc,t)
R25:W31	R25	=AREPORT(C4#,lc,,tc,t)
Z25:AD32	Z25	=AREPORT(C4#,lc,h,,t)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
h	='AREPORT post'!$G$2:$J$2	C25, K25, L4, L13, S13, Z25
lc	='AREPORT post'!$E$4:$E$9	C25, L4, R25, S4, Z4, Z25
t	='AREPORT post'!$G$11:$J$11	K25, R25, S13, Z4, Z13, Z25
tc	='AREPORT post'!$L$4:$L$9	C25, K25, L13, R25, S4, Z13

 

[Xlambda]

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
1			h									corner/corner value functionality (array or array constant)/(single cell or single value)
2			A	B	C	D						order of filling,cr first, fills as many corners there are with as many elements cr has, then crv fills only
3	lc		ma					tc				if lower right corner exists, crv is not omitted and it was not filled by previous operation.
4	a		1	2	3	4		10				To leave a corner empty , null string ("")
5	b		5	6	7	8		26				1.) single "vectors" do not create corners, cr/crv, omitted or not , will do nothing
6	c		9	10	11	12		42				2.) double vectors can create a corner or none
7	d		13	14	15	16		58				3.) triple vectors creates 2 corners
8	e		17	18	19	20		74				4.) all vectors, creates all 4 corners
9	f		21	22	23	24		90
10			t					crv				=AREPORT(C4#,lc,h,tc,t,,300)							=AREPORT(C4#,lc,h,tc,t,"R",H11)							=AREPORT(C4#,lc,h,tc,t,{"R","","T"},H11)
11			66	72	78	84		300					A	B	C	D			R	A	B	C	D			R	A	B	C	D
12												a	1	2	3	4	10		a	1	2	3	4	10		a	1	2	3	4	10
13			cr									b	5	6	7	8	26		b	5	6	7	8	26		b	5	6	7	8	26
14			R	TC	T							c	9	10	11	12	42		c	9	10	11	12	42		c	9	10	11	12	42
15												d	13	14	15	16	58		d	13	14	15	16	58		d	13	14	15	16	58
16												e	17	18	19	20	74		e	17	18	19	20	74		e	17	18	19	20	74
17												f	21	22	23	24	90		f	21	22	23	24	90		f	21	22	23	24	90
18													66	72	78	84	300			66	72	78	84	300		T	66	72	78	84	300
19
20							=AREPORT(C4#,lc,h,tc,t,{"R","TC"},H11)									=AREPORT(C4#,lc,h,tc,t,{"R","TC","","X"},H11)										=AREPORT(C4#,lc,h,tc,t,{"R","TC","T"},H11)
21								R	A	B	C	D	TC			R	A	B	C	D	TC					R	A	B	C	D	TC
22								a	1	2	3	4	10			a	1	2	3	4	10					a	1	2	3	4	10
23								b	5	6	7	8	26			b	5	6	7	8	26					b	5	6	7	8	26
24								c	9	10	11	12	42			c	9	10	11	12	42					c	9	10	11	12	42
25								d	13	14	15	16	58			d	13	14	15	16	58					d	13	14	15	16	58
26								e	17	18	19	20	74			e	17	18	19	20	74					e	17	18	19	20	74
27								f	21	22	23	24	90			f	21	22	23	24	90					f	21	22	23	24	90
28									66	72	78	84	300				66	72	78	84	X					T	66	72	78	84	300
29
AREPORT post 3

Cell Formulas
Range	Formula
C4:F9	C4	=SEQUENCE(6,4)
L10,P20,Z20,S10,Z10	L10	=FORMULATEXT(L11)
L11:Q18	L11	=AREPORT(C4#,lc,h,tc,t,,300)
S11:X18	S11	=AREPORT(C4#,lc,h,tc,t,"R",H11)
Z11:AE18	Z11	=AREPORT(C4#,lc,h,tc,t,{"R","","T"},H11)
G20	G20	=FORMULATEXT(H21)
H21:M28	H21	=AREPORT(C4#,lc,h,tc,t,{"R","TC"},H11)
P21:U28	P21	=AREPORT(C4#,lc,h,tc,t,{"R","TC","","X"},H11)
Z21:AE28	Z21	=AREPORT(C4#,lc,h,tc,t,{"R","TC","T"},H11)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
h	='AREPORT post'!$G$2:$J$2	H21, L11, P21, S11, Z11, Z21
lc	='AREPORT post'!$E$4:$E$9	H21, L11, P21, S11, Z11, Z21
t	='AREPORT post'!$G$11:$J$11	H21, L11, P21, S11, Z11, Z21
tc	='AREPORT post'!$L$4:$L$9	H21, L11, P21, S11, Z11, Z21

 

[Xlambda]

Same function, same arguments, different approach, better adapted for reports with multiple trailing columns or total rows. Calls APP2H , APP2V , AFILL . None of the new functions used.

=LAMBDA(ma,[lc],[h],[tc],[t],[cr],[crv],
    LET(il,ISOMITTED(lc),ih,ISOMITTED(h),ic,ISOMITTED(tc),it,ISOMITTED(t),
       a,IF(il,ma,APP2H(lc,ma)),b,IF(ic,a,APP2H(a,tc)),c,IF(ih,b,APP2V(h,b,,il-1)),
       d,IF(it,c,APP2V(c,t,,1-il)),e,AFILL(d,crv,ROWS(d),,COLUMNS(d),),
       AFILL(e,cr)
    )
)

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
1	Task: build PT style, complex annual report for 5 products , main array "ma" previously calculated, reflecting totals/months, totals/products, average/months, max/products
2
3			header, (months)
4			=TEXT(SEQUENCE(,12,,31),"mmm")
5		h	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
6																1st trailing column, (totals/products)							2nd trailing column, (max/products)
7	lc		ma (main array)												=BYROW(C8:N12,LAMBDA(a,SUM(a)))							=BYROW(C8:N12,LAMBDA(a,MAX(a)))
8	Prod 1		93	83	97	70	174	30	148	134	106	12	27	179	1st tc		1153				2nd tc		179
9	Prod 2		147	189	136	42	26	110	12	135	11	123	156	149			1236						189
10	Prod 3		161	59	173	30	34	104	25	140	124	189	136	184			1359						189
11	Prod 4		38	182	18	182	99	176	26	64	183	18	140	116			1242						183
12	Prod 5		168	21	171	10	171	86	135	166	130	176	74	156			1464						176
13
14			1st total row (totals/months)															step 1
15			=BYCOL(C8:N12,LAMBDA(a,SUM(a)))															=AREPORT(C8:N12,,,Q8#,C16#,,L18)
16		1st t	607	534	595	334	504	506	346	639	554	518	533	784				93	83	97	70	174	30	148	134	106	12	27	179	1153
17												=SUM(C16#)						147	189	136	42	26	110	12	135	11	123	156	149	1236
18			2nd total row (average/months)							Grand Total		6454						161	59	173	30	34	104	25	140	124	189	136	184	1359
19			=BYCOL(C8:N12,LAMBDA(a,AVERAGE(a)))															38	182	18	182	99	176	26	64	183	18	140	116	1242
20		2nd t	121	107	119	66.8	101	101	69.2	128	111	104	107	157				168	21	171	10	171	86	135	166	130	176	74	156	1464
21																		607	534	595	334	504	506	346	639	554	518	533	784	6454
22		corners array
23		={2021,"Total","Max","Total","","Avrg"}																step 2
24		2021	Total	Max	Total		Avrg											=AREPORT(R16#,A8:A12,C5#,W8#,C20#,B24#)
25																		2021	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total	Max
26																		Prod 1	93	83	97	70	174	30	148	134	106	12	27	179	1153	179
27	single cell reports																	Prod 2	147	189	136	42	26	110	12	135	11	123	156	149	1236	189
28																		Prod 3	161	59	173	30	34	104	25	140	124	189	136	184	1359	189
29	=AREPORT(AREPORT(C8:N12,,,Q8#,C16#,,L18),A8:A12,C5#,W8#,C20#,B24#)																	Prod 4	38	182	18	182	99	176	26	64	183	18	140	116	1242	183
30	2021	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total	Max			Prod 5	168	21	171	10	171	86	135	166	130	176	74	156	1464	176
31	Prod 1	93	83	97	70	174	30	148	134	106	12	27	179	1153	179			Total	607	534	595	334	504	506	346	639	554	518	533	784	6454
32	Prod 2	147	189	136	42	26	110	12	135	11	123	156	149	1236	189			Avrg	121	107	119	66.8	101	101	69.2	128	111	104	107	157
33	Prod 3	161	59	173	30	34	104	25	140	124	189	136	184	1359	189
34	Prod 4	38	182	18	182	99	176	26	64	183	18	140	116	1242	183
35	Prod 5	168	21	171	10	171	86	135	166	130	176	74	156	1464	176
36	Total	607	534	595	334	504	506	346	639	554	518	533	784	6454
37	Avrg	121	107	119	66.8	101	101	69.2	128	111	104	107	157
38
39	swapping trailing columns and total rows
40
41	=AREPORT(AREPORT(C8:N12,,,W8#,C20#),A8:A12,C5#,Q8#,C16#,S44#,L18)
42	2021	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Max	Total				corners array,swapping variant
43	Prod 1	93	83	97	70	174	30	148	134	106	12	27	179	179	1153				={2021,"Max","Total","Avrg","","GT","Total","GT"}
44	Prod 2	147	189	136	42	26	110	12	135	11	123	156	149	189	1236				2021	Max	Total	Avrg		GT	Total	GT
45	Prod 3	161	59	173	30	34	104	25	140	124	189	136	184	189	1359
46	Prod 4	38	182	18	182	99	176	26	64	183	18	140	116	183	1242
47	Prod 5	168	21	171	10	171	86	135	166	130	176	74	156	176	1464
48	Avrg	121	107	119	66.8	101	101	69.2	128	111	104	107	157		GT
49	Total	607	534	595	334	504	506	346	639	554	518	533	784	GT	6454
50
AREPORT post 4

Cell Formulas
Range	Formula
C4,S43,A41,A29,R24,B23,C19,L17,R15,C15	C4	=FORMULATEXT(C5)
C5:N5	C5	=TEXT(SEQUENCE(,12,,31),"mmm")
O7	O7	=FORMULATEXT(Q8)
V7	V7	=FORMULATEXT(W8)
Q8:Q12	Q8	=BYROW(C8:N12,LAMBDA(a,SUM(a)))
W8:W12	W8	=BYROW(C8:N12,LAMBDA(a,MAX(a)))
C16:N16	C16	=BYCOL(C8:N12,LAMBDA(a,SUM(a)))
R16:AD21	R16	=AREPORT(C8:N12,,,Q8#,C16#,,L18)
L18	L18	=SUM(C16#)
C20:N20	C20	=BYCOL(C8:N12,LAMBDA(a,AVERAGE(a)))
B24:G24	B24	={2021,"Total","Max","Total","","Avrg"}
R25:AF32	R25	=AREPORT(R16#,A8:A12,C5#,W8#,C20#,B24#)
A30:O37	A30	=AREPORT(AREPORT(C8:N12,,,Q8#,C16#,,L18),A8:A12,C5#,W8#,C20#,B24#)
A42:O49	A42	=AREPORT(AREPORT(C8:N12,,,W8#,C20#),A8:A12,C5#,Q8#,C16#,S44#,L18)
S44:Z44	S44	={2021,"Max","Total","Avrg","","GT","Total","GT"}
Dynamic array formulas.

 

[st001]

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

But what if I have a multiple columns, with a mixture of numerical and text fields like this:

Not sure if that makes sense. I'm sure it can be done with a Lambda (without using P.Q.)....

Table A

Table B

In Table A, but not Table B

In Table B, but not Table A

Project ID

Project Name

Business Unit

Project ID

Project Name

Business Unit

Project ID

Project Name

Business Unit

Project ID

Project Name

Business Unit

an Array ->

1

Cosmic

AB

an Array ->

1

Cosmic

AB

??? - Formula

??? - Formula

2

SpaceX

DE

5

Quantum

GG

3

StarBird

FG

6

Odyssey Quest

AB

4

FalconX

DE

4

FalconX

DE

 

[st001]

This half works, but for some reason, the COUNTIFS function doesn't like working on the two list arrays I give it.
If I show the two list arrays in the actual workbook, the COUNTIFS function works as expected, just not when the list arrays are stored as variables. Not sure why.

=LET(
TableA, B5:D9,
TableA_List, BYROW(TableA, LAMBDA(row, TEXTJOIN("~^~", FALSE, row))),
TableB, F5:H8,
TableB_List, BYROW(TableB, LAMBDA(row, TEXTJOIN("~^~", FALSE, row))),
result, RBYROW(
UNIQUE(FILTER(TableA_List, COUNTIFS(TableB_List, TableA_List) = 0)),
LAMBDA(row, TEXTSPLIT(row, "~^~"))
),
COUNTIFS(TableB_List, TableA_List)
)

 

[st001]

Its not the neatest solution, but this did work! (Countifs cannot accept arrays as the first argument, only ranges)

=LET(
    TableA, B5:D9,
    TableA_List, BYROW(TableA, LAMBDA(row, TEXTJOIN("~^~", FALSE, row))),
    TableB, F5:H8,
    TableB_List, BYROW(TableB, LAMBDA(row, TEXTJOIN("~^~", FALSE, row))),
    TableA_List_Expanded, IF(
        ROWS(TableB_List) > ROWS(TableA_List),
        EXPAND(TableA_List, ROWS(TableB_List), 1, "Empty"),
        TableA_List
    ),
    TableB_List_Expanded, IF(
        ROWS(TableA_List) > ROWS(TableB_List),
        EXPAND(TableB_List, ROWS(TableA_List), 1, "Empty"),
        TableB_List
    ),
    result, RBYROW(
        UNIQUE(
            FILTER(
                TableA_List_Expanded,
                BYROW(TableA_List_Expanded <> TableB_List_Expanded, LAMBDA(row, IFERROR(AND(row), "FALSE")))
            )
        ),
        LAMBDA(row, TEXTSPLIT(row, "~^~"))
    ),
    result
)

 

[jaeiow]

=LET(
TableA, B5:D8,
TableA_List, BYROW(TableA, LAMBDA(row, TEXTJOIN("~^~", FALSE, row))),
TableB, F5:H8,
TableB_List, BYROW(TableB, LAMBDA(row, TEXTJOIN("~^~", FALSE, row))),
TEST, UNIQUE(VSTACK(TableA_List,TableB_List),,1),
result, RBYROW(UNIQUE(FILTER(TableA_List,ISNUMBER(XMATCH(TableA_List,TEST)))),
LAMBDA(row, TEXTSPLIT(row, "~^~"))),result)

 

[st001]

Yours is shorter and gives a more reliable output!
Thank you!

 

[jaeiow]

Now I have a whole thought of a series of functions measuring novelty, but rearranged columns would break this. I wonder if my (TOFILE) and (BACKTOTABLE) functions could solve this.