AREPORT

AREPORT(ma,[lc],[h],[tc],[t],[cr],[crv])
ma
main array
[lc]
optional, leading 1 column array
[h]
optional, headers 1 row array
[tc]
optional, trailing 1 column array
[t]
optional, totals 1 row array
[cr]
optional, corners array, constant array, any shape
[crv]
optional, single value
builds Array Reports PT style. !! NEW !! BYROW , BYCOL
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
AREPORT builds Array Reports PT style. !! NEW !! BYROW , BYCOL . Calls APP2H , APP2V , AFILL
Excel Formula: 
=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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1clock map of arg. sequencehsingle "vectors"
2clockwise from 9h to 6hABCD
3lcmatc=AREPORT(G4#,lc)=AREPORT(G4#,,h)
4h(12h)a123410a1234ABCD
5lc(9h)⏲tc(3h)b567826b56781234
6t(6h)c910111242c91011125678
7d1314151658d131415169101112
8lc,h,tc,t,cr,crve1718192074e1718192013141516
99h,12h,3h,6h,,f2122232490f2122232417181920
10tcrv21222324
11ma: main array66727884300
12lc: leading column=AREPORT(G4#,,,tc)=AREPORT(G4#,,,,t)
13h: headerscr1234101234
14tc trailing columnRTCT5678265678
15t: totals9101112429101112
16cr: cornersall "vectors"131415165813141516
17crv: corners value171819207417181920
18=AREPORT(G4#,lc,h,tc,t,G14:J14,L11)212223249021222324
19name rangesRABCDTC66727884
20lc: E4:E9a123410
21h: G2:J2b567826
22tc: L4:L9c910111242
23t: G11:J11d1314151658
24e1718192074
25f2122232490
26T66727884300
27
28
AREPORT post
Cell Formulas
RangeFormula
O3,F18,V12,O12,V3O3=FORMULATEXT(O4)
G4:J9G4=SEQUENCE(6,4)
O4:S9O4=AREPORT(G4#,lc)
V4:Y10V4=AREPORT(G4#,,h)
O13:S18O13=AREPORT(G4#,,,tc)
V13:Y19V13=AREPORT(G4#,,,,t)
F19:K26F19=AREPORT(G4#,lc,h,tc,t,G14:J14,L11)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
h='AREPORT post'!$G$2:$J$2V4, F19
lc='AREPORT post'!$E$4:$E$9O4, F19
t='AREPORT post'!$G$11:$J$11V13, F19
tc='AREPORT post'!$L$4:$L$9O13, F19
 
Upvote 0
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1hdouble "vectors"
2ABCD
3lcmatc=AREPORT(C4#,lc,h)=AREPORT(C4#,lc,,tc)=AREPORT(C4#,lc,,,t)
4a123410 ABCDa123410a1234
5b567826a1234b567826b5678
6c910111242b5678c910111242c9101112
7d1314151658c9101112d1314151658d13141516
8e1718192074d13141516e1718192074e17181920
9f2122232490e17181920f2122232490f21222324
10tcrvf2122232466727884
1166727884300
12=AREPORT(C4#,,h,tc)=AREPORT(C4#,,h,,t)=AREPORT(C4#,,,tc,t)
13crABCDABCD123410
14RTCT1234101234567826
155678265678910111242
1691011124291011121314151658
171314151658131415161718192074
181718192074171819202122232490
1921222324902122232466727884
2066727884
21
22triple "vectors"
23
24=AREPORT(C4#,lc,h,tc)=AREPORT(C4#,,h,tc,t)=AREPORT(C4#,lc,,tc,t)=AREPORT(C4#,lc,h,,t)
25 ABCDABCDa123410 ABCD
26a123410123410b567826a1234
27b567826567826c910111242b5678
28c910111242910111242d1314151658c9101112
29d13141516581314151658e1718192074d13141516
30e17181920741718192074f2122232490e17181920
31f2122232490212223249066727884f21222324
326672788466727884
33
34
AREPORT post 2
Cell Formulas
RangeFormula
L3,Z24,R24,K24,C24,Z12,S12,L12,Z3,S3L3=FORMULATEXT(L4)
C4:F9C4=SEQUENCE(6,4)
L4:P10L4=AREPORT(C4#,lc,h)
S4:X9S4=AREPORT(C4#,lc,,tc)
Z4:AD10Z4=AREPORT(C4#,lc,,,t)
L13:P19L13=AREPORT(C4#,,h,tc)
S13:V20S13=AREPORT(C4#,,h,,t)
Z13:AD19Z13=AREPORT(C4#,,,tc,t)
C25:H31C25=AREPORT(C4#,lc,h,tc)
K25:O32K25=AREPORT(C4#,,h,tc,t)
R25:W31R25=AREPORT(C4#,lc,,tc,t)
Z25:AD32Z25=AREPORT(C4#,lc,h,,t)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
h='AREPORT post'!$G$2:$J$2C25, K25, L4, L13, S13, Z25
lc='AREPORT post'!$E$4:$E$9C25, L4, R25, S4, Z4, Z25
t='AREPORT post'!$G$11:$J$11K25, R25, S13, Z4, Z13, Z25
tc='AREPORT post'!$L$4:$L$9C25, K25, L13, R25, S4, Z13
 
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1hcorner/corner value functionality (array or array constant)/(single cell or single value)
2ABCDorder of filling,cr first, fills as many corners there are with as many elements cr has, then crv fills only
3lcmatcif lower right corner exists, crv is not omitted and it was not filled by previous operation.
4a123410To leave a corner empty , null string ("")
5b5678261.) single "vectors" do not create corners, cr/crv, omitted or not , will do nothing
6c9101112422.) double vectors can create a corner or none
7d13141516583.) triple vectors creates 2 corners
8e17181920744.) all vectors, creates all 4 corners
9f2122232490
10tcrv=AREPORT(C4#,lc,h,tc,t,,300)=AREPORT(C4#,lc,h,tc,t,"R",H11)=AREPORT(C4#,lc,h,tc,t,{"R","","T"},H11)
1166727884300 ABCDRABCDRABCD
12a123410a123410a123410
13crb567826b567826b567826
14RTCTc910111242c910111242c910111242
15d1314151658d1314151658d1314151658
16e1718192074e1718192074e1718192074
17f2122232490f2122232490f2122232490
186672788430066727884300T66727884300
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)
21RABCDTCRABCDTCRABCDTC
22a123410a123410a123410
23b567826b567826b567826
24c910111242c910111242c910111242
25d1314151658d1314151658d1314151658
26e1718192074e1718192074e1718192074
27f2122232490f2122232490f2122232490
286672788430066727884XT66727884300
29
AREPORT post 3
Cell Formulas
RangeFormula
C4:F9C4=SEQUENCE(6,4)
L10,P20,Z20,S10,Z10L10=FORMULATEXT(L11)
L11:Q18L11=AREPORT(C4#,lc,h,tc,t,,300)
S11:X18S11=AREPORT(C4#,lc,h,tc,t,"R",H11)
Z11:AE18Z11=AREPORT(C4#,lc,h,tc,t,{"R","","T"},H11)
G20G20=FORMULATEXT(H21)
H21:M28H21=AREPORT(C4#,lc,h,tc,t,{"R","TC"},H11)
P21:U28P21=AREPORT(C4#,lc,h,tc,t,{"R","TC","","X"},H11)
Z21:AE28Z21=AREPORT(C4#,lc,h,tc,t,{"R","TC","T"},H11)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
h='AREPORT post'!$G$2:$J$2H21, L11, P21, S11, Z11, Z21
lc='AREPORT post'!$E$4:$E$9H21, L11, P21, S11, Z11, Z21
t='AREPORT post'!$G$11:$J$11H21, L11, P21, S11, Z11, Z21
tc='AREPORT post'!$L$4:$L$9H21, L11, P21, S11, Z11, Z21
 
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.
Excel Formula: 
=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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1Task: build PT style, complex annual report for 5 products , main array "ma" previously calculated, reflecting totals/months, totals/products, average/months, max/products
2
3header, (months)
4=TEXT(SEQUENCE(,12,,31),"mmm")
5hJanFebMarAprMayJunJulAugSepOctNovDec
61st trailing column, (totals/products)2nd trailing column, (max/products)
7lcma (main array)=BYROW(C8:N12,LAMBDA(a,SUM(a)))=BYROW(C8:N12,LAMBDA(a,MAX(a)))
8Prod 1938397701743014813410612271791st tc11532nd tc179
9Prod 2147189136422611012135111231561491236189
10Prod 3161591733034104251401241891361841359189
11Prod 43818218182991762664183181401161242183
12Prod 5168211711017186135166130176741561464176
13
141st total row (totals/months)step 1
15=BYCOL(C8:N12,LAMBDA(a,SUM(a)))=AREPORT(C8:N12,,,Q8#,C16#,,L18)
161st t607534595334504506346639554518533784938397701743014813410612271791153
17=SUM(C16#)147189136422611012135111231561491236
182nd total row (average/months)Grand Total6454161591733034104251401241891361841359
19=BYCOL(C8:N12,LAMBDA(a,AVERAGE(a)))3818218182991762664183181401161242
202nd t12110711966.810110169.2128111104107157168211711017186135166130176741561464
216075345953345045063466395545185337846454
22corners array
23={2021,"Total","Max","Total","","Avrg"}step 2
242021TotalMaxTotalAvrg=AREPORT(R16#,A8:A12,C5#,W8#,C20#,B24#)
252021JanFebMarAprMayJunJulAugSepOctNovDecTotalMax
26Prod 1938397701743014813410612271791153179
27single cell reportsProd 2147189136422611012135111231561491236189
28Prod 3161591733034104251401241891361841359189
29=AREPORT(AREPORT(C8:N12,,,Q8#,C16#,,L18),A8:A12,C5#,W8#,C20#,B24#)Prod 43818218182991762664183181401161242183
302021JanFebMarAprMayJunJulAugSepOctNovDecTotalMaxProd 5168211711017186135166130176741561464176
31Prod 1938397701743014813410612271791153179Total6075345953345045063466395545185337846454
32Prod 2147189136422611012135111231561491236189Avrg12110711966.810110169.2128111104107157
33Prod 3161591733034104251401241891361841359189
34Prod 43818218182991762664183181401161242183
35Prod 5168211711017186135166130176741561464176
36Total6075345953345045063466395545185337846454
37Avrg12110711966.810110169.2128111104107157
38
39swapping trailing columns and total rows
40
41=AREPORT(AREPORT(C8:N12,,,W8#,C20#),A8:A12,C5#,Q8#,C16#,S44#,L18)
422021JanFebMarAprMayJunJulAugSepOctNovDecMaxTotalcorners array,swapping variant
43Prod 1938397701743014813410612271791791153={2021,"Max","Total","Avrg","","GT","Total","GT"}
44Prod 21471891364226110121351112315614918912362021MaxTotalAvrgGTTotalGT
45Prod 3161591733034104251401241891361841891359
46Prod 43818218182991762664183181401161831242
47Prod 5168211711017186135166130176741561761464
48Avrg12110711966.810110169.2128111104107157GT
49Total607534595334504506346639554518533784GT6454
50
AREPORT post 4
Cell Formulas
RangeFormula
C4,S43,A41,A29,R24,B23,C19,L17,R15,C15C4=FORMULATEXT(C5)
C5:N5C5=TEXT(SEQUENCE(,12,,31),"mmm")
O7O7=FORMULATEXT(Q8)
V7V7=FORMULATEXT(W8)
Q8:Q12Q8=BYROW(C8:N12,LAMBDA(a,SUM(a)))
W8:W12W8=BYROW(C8:N12,LAMBDA(a,MAX(a)))
C16:N16C16=BYCOL(C8:N12,LAMBDA(a,SUM(a)))
R16:AD21R16=AREPORT(C8:N12,,,Q8#,C16#,,L18)
L18L18=SUM(C16#)
C20:N20C20=BYCOL(C8:N12,LAMBDA(a,AVERAGE(a)))
B24:G24B24={2021,"Total","Max","Total","","Avrg"}
R25:AF32R25=AREPORT(R16#,A8:A12,C5#,W8#,C20#,B24#)
A30:O37A30=AREPORT(AREPORT(C8:N12,,,Q8#,C16#,,L18),A8:A12,C5#,W8#,C20#,B24#)
A42:O49A42=AREPORT(AREPORT(C8:N12,,,W8#,C20#),A8:A12,C5#,Q8#,C16#,S44#,L18)
S44:Z44S44={2021,"Max","Total","Avrg","","GT","Total","GT"}
Dynamic array formulas.
 
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.)....:confused:

Table ATable BIn Table A, but not Table BIn Table B, but not Table A
Project IDProject NameBusiness UnitProject IDProject NameBusiness UnitProject IDProject NameBusiness UnitProject IDProject NameBusiness Unit
an Array ->1CosmicABan Array ->1CosmicAB??? - Formula
??? - Formula
2SpaceXDE5QuantumGG
3StarBirdFG6Odyssey QuestAB
4FalconXDE4FalconXDE
 
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)
)
 
Its not the neatest solution, but this did work! (Countifs cannot accept arrays as the first argument, only ranges)

Excel Formula: 
=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
)
 
Last edited by a moderator:
Excel Formula: 
=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)
 
Yours is shorter and gives a more reliable output!
Thank you! 🙏
 
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.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top