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

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
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
Excel Formula:
=LAMBDA(a,b,[k],LET(u,UNIQUE(VSTACK(a,b)),IF(k,UNIQUE(VSTACK(a,u),,1),UNIQUE(VSTACK(b,u),,1))))
Book1
ABCDEFGHI
1
2ab
31CosmicAB1CosmicAB
42SpaceXDE5QuantumCG
53StarBirdFG6Odyssey QuestAB
64FalconXDE4FalconXDE
7
8
9k, omitted => in a not in bk,1 => in b not in a
10=AB(B3:D6,F3:H6)=AB(B3:D6,F3:H6,1)
112SpaceXDE5QuantumCG
123StarBirdFG6Odyssey QuestAB
13
14
Sheet1
Cell Formulas
RangeFormula
B10,F10B10=FORMULATEXT(B11)
B11:D12B11=AB(B3:D6,F3:H6)
F11:H12F11=AB(B3:D6,F3:H6,1)
Dynamic array formulas.
 
Amazing!
I will test this, but looks a lot simpler than I thought. I learn everyday!
Thank you. 🙏
🤚
 

Forum statistics

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

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