AFILL

AFILL(a,b,[r1w],[r2w],[c1l],[c2l])
a
array, target
b
array, replacement array
[r1w]
optional,1st row index value "a" array, if omitted 1
[r2w]
optional, 2nr row index value "a" array, if omitted rows(a)
[c1l]
optional,1st clm index value "a" array, if omitted 1
[c2l]
optional, 2nr clm index value "a" array, if omitted clms(a)
AFILL replaces all blanks/null strings of an array/array area, with other array values. !! NEW !! MAKEARRAY , SCAN
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
AFILL replaces all blanks/null strings of an array/array area, with other array values. !! NEW !! MAKEARRAY , SCAN . Calls AFLAT.
Exactly same functionality like in APUZZLE, only this time, only blanks/null strings will be replaced. If we want to change the condition variable, (cd,a="") , it's the first one after LET.
Excel Formula: 
=LAMBDA(a,b,[r1w],[r2w],[c1l],[c2l],
    LET(cd,a="",fb,AFLAT(b,1),r,ROWS(a),c,COLUMNS(a),x,MEDIAN(1,IF(r1w,r1w,1),r),y,MEDIAN(1,IF(r2w,r2w,r),r),z,MEDIAN(1,IF(c1l,c1l,1),c),w,MEDIAN(1,IF(c2l,c2l,c),c),
      m,cd*MAKEARRAY(r,c,LAMBDA(r,c,IF(x>y,(r>=x)+(r<=y),(r>=x)*(r<=y))*IF(z>w,(c>=z)+(c<=w),(c>=z)*(c<=w)))),
      s,IFERROR(INDEX(fb,SCAN(0,m,LAMBDA(v,a,v+a))),""),
      IF(m,s,IF(a="","",a))
    )
)
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Absolute: Every time we needed to write single cell formulas dynamic reports, to look like PT/PQ reports, more than 1/3 of formula length is allocated to build those "puzzles" of appending headers, full columns, grand total rows and columns, corners etc. All this set of functions APP2H, APP2V with offset capabilities, APUZZLE, AFILL , will make this constructions a breeze.
2AFILL replaces all blanks/null strings of an array/array area, with other array valuesblanks/null strings
3r1w,r2w,c1l,c2l arguments, same functionality as in APUZZLE function, selects area or areas of an array. Data outside area selected will remain unchanged.
4If all arguments are omitted, all blanks of "a" will be filled with second array "b" values from left to right, top to bottom.
5Because only blanks will be replaced we do not need to be as precise with the area selections, like in APUZZLE functions that replaces all the area values
6ab
7123456 8910AB
81114151617181920CD 
921222324262730#N/AEF
1031343537383940GHI
11
12all arg.omittedif we want to exclude blanks of "b" array, we use as "b" arg. AFLAT(b)
13=AFILL(B7:K10,M7:O10)=AFILL(B7:K10,AFLAT(M7:O10))
14123456A8910123456A8910
1511B1415161718192011BC14151617181920
1621222324C2627D3021222324D2627EF30
1731E3435F3738394031GH3435I37383940
18
19Note: unlike APUZZLE (that replaces whole selected areas ),with AFILL we can select bigger areas, since only blanks will be replaced
20
21,,8,4,3 (col arguments omitted , all columns will be selected)
22=AFILL(B7:K10,M7:O10,,,8,4)=AFILL(B7:K10,M7:O10,,3)
231234568910123456A8910
2411A1415161718192011B14151617181920
25212223242627BC30↑21222324C2627D30
2631D343537383940<=331343537383940
27←<=4>=8→
28"puzzle functionality"
29ab=AFILL(B30:E34,G30:H32)
301234AAA1234
3156BBBA56AA
3278CCCB78BB
33910C910CC
341112131411121314
35Is like appending 3 arrays but filling only the gaps
36if "gaps" of "a" are offset
37ab=AFILL(B38:E42,G38:H40)
38123AAA123A
3945BBBAA45B
4067CCCBB67C
418810CC8810
421112131411121314
43not expected outcome, columns are swapped due to the filling pattern, left to right, top to bottom
44for this situations, nested AFILL cool trick to solve this in one take, is to swap b columns
45=AFILL(AFILL(B38:E42,G38:G40,,,,1),H38:H40)=AFILL(B38:E42,INDEX(G38:H40,SEQUENCE(3),{2,1}))
46123AA123AA
47A45BBA45BB
48B67CCB67CC
49C8810C8810
501112131411121314
51
52funny trick - it's an obvious and flagrant mistake to try to sneak a variable in a constant array, like in {"a","b","c";1,2,x} .
53Always keeping formulas as short as possible, not over declaring variables, surprised myself several times trying it.?
54Then, had to use CHOOSE,is ok, but,Now with AFILL is a breeze, we only have to place
55we need to put the values one by one ?null strings where we want variables, and fill them ✌
56=LET(x,3,CHOOSE({1,2,3;4,5,6},"a","b","c",1,2,x))=LET(x,3,AFILL({"a","b","c";1,2,""},x))
57abcabc
58123123
59
60-if we need this several times, for dif variables, writing many CHOOSE becomes a mess, with AFILL we can declare a variable,
61the constant array with a "gap", and "fill" it with ease as many time as we want, like in this construction
62 =….x,3,y,4,c,{"a","b","c";1,2,""},xf,AFILL(c,x),yf,AFILL(c,y)...
63
AFILL post 1
Cell Formulas
RangeFormula
H7,O8H7=""
M9M9=NA()
B13,B56,J56,J45,B45,J37,J29,N22,B22,M13B13=FORMULATEXT(B14)
B14:K17B14=AFILL(B7:K10,M7:O10)
M14:V17M14=AFILL(B7:K10,AFLAT(M7:O10))
B23:K26B23=AFILL(B7:K10,M7:O10,,,8,4)
N23:W26N23=AFILL(B7:K10,M7:O10,,3)
J30:M34,J38:M42J30=AFILL(B30:E34,G30:H32)
B46:E50B46=AFILL(AFILL(B38:E42,G38:G40,,,,1),H38:H40)
J46:M50J46=AFILL(B38:E42,INDEX(G38:H40,SEQUENCE(3),{2,1}))
B57:D58B57=LET(x,3,CHOOSE({1,2,3;4,5,6},"a","b","c",1,2,x))
J57:L58J57=LET(x,3,AFILL({"a","b","c";1,2,""},x))
Dynamic array formulas.
 
Upvote 0
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Task: defining a report variable inside a formula that previously calculated variables for all components arrays needed to build a lookalike PT report puzzleother functions on minisheet
2Example: elements of annual report for 5 ProductsAPP2H
3products("p")main array "ar"right column, Total ("rc")APP2V
4par=BYROW(C5:N9,LAMBDA(a,SUM(a)))APUZZLE
5Prod 193839770174301481341061227179rc1153
6Prod 2147189136422611012135111231561491236corners array ("ca")
7Prod 3161591733034104251401241891361841359Grand Total cell ("gt")ca=AFILL({"2021","Total";"Total",""},R9)
8Prod 43818218182991762664183181401161242gt=SUM(P5#)=SUM(C15#)2021Total
9Prod 51682117110171861351661301767415614646454or6454Total6454
10for month headers ("h") , inside formulas I use this:
11=TEXT(SEQUENCE(,12,,31),"mmm")
12hJanFebMarAprMayJunJulAugSepOctNovDecstep 4. - single cell formula
13bottom row,Total ("br")=AFILL(APP2V(APP2V(C12#,APP2H(A5:A9,APP2H(C5:N9,P5#)),,-1),C15#,,1),X8#)
14=BYCOL(C5:N9,LAMBDA(a,SUM(a)))
15br6075345953345045063466395545185337842021JanFebMarAprMayJunJulAugSepOctNovDecTotal
16Prod 1938397701743014813410612271791153
17step 1. - append horizontal arrays p,ar,rcProd 2147189136422611012135111231561491236
18=APP2H(A5:A9,APP2H(C5:N9,P5#))Prod 3161591733034104251401241891361841359
19Prod 1938397701743014813410612271791153Prod 43818218182991762664183181401161242
20Prod 2147189136422611012135111231561491236Prod 5168211711017186135166130176741561464
21Prod 3161591733034104251401241891361841359Total6075345953345045063466395545185337846454
22Prod 43818218182991762664183181401161242
23Prod 5168211711017186135166130176741561464- this is how will look inside a formula
24r,AFILL(APP2V(APP2V(h,APP2H(p,APP2H(ar,rc)),,-1),br,,1),ca),gt))
25step 2. - appending headers and bottom row, (offset functionality comes handy)
26=APP2V(APP2V(C12#,B19#,,-1),C15#,,1)
27 JanFebMarAprMayJunJulAugSepOctNovDecIf we need to change the Totals with averages(months) and max(products)
28Prod 1938397701743014813410612271791153=BYCOL(R16:AC20,LAMBDA(a,AVERAGE(a)))
29Prod 2147189136422611012135111231561491236avrg121.4106.811966.8100.8101.269.2127.8110.8103.6106.6156.8
30Prod 3161591733034104251401241891361841359=TRANSPOSE(BYROW(R16:AC20,LAMBDA(a,MAX(a))))
31Prod 43818218182991762664183181401161242max179189189183176new corners array
32Prod 51682117110171861351661301767415614642021Max
33607534595334504506346639554518533784Avrg
34
35step 3. - fill the corners with corners array=APUZZLE(APUZZLE(APUZZLE(Q15#,R29#,7,,2,13),R31#,2,6,14,),AA32:AB33,7,1,14,1)
36=AFILL(B27#,X8#)
372021JanFebMarAprMayJunJulAugSepOctNovDecTotal2021JanFebMarAprMayJunJulAugSepOctNovDecMax
38Prod 1938397701743014813410612271791153Prod 193839770174301481341061227179179
39Prod 2147189136422611012135111231561491236Prod 214718913642261101213511123156149189
40Prod 3161591733034104251401241891361841359Prod 316159173303410425140124189136184189
41Prod 43818218182991762664183181401161242Prod 4381821818299176266418318140116183
42Prod 5168211711017186135166130176741561464Prod 516821171101718613516613017674156176
43Total6075345953345045063466395545185337846454Avrg121.4106.811966.8100.8101.269.2127.8110.8103.6106.6156.8
44
45Note: There are a lot of approaches for solving the "puzzle", this is only one of them
46
AFILL post 2
Cell Formulas
RangeFormula
P4,B36,R30,R28,B26,B18,C14,C11,U8,R8,X7P4=FORMULATEXT(P5)
P5:P9P5=BYROW(C5:N9,LAMBDA(a,SUM(a)))
X8:Y9X8=AFILL({"2021","Total";"Total",""},R9)
R9R9=SUM(P5#)
U9U9=SUM(C15#)
C12:N12C12=TEXT(SEQUENCE(,12,,31),"mmm")
Q13,Q35Q13=FORMULATEXT(Q15)
C15:N15C15=BYCOL(C5:N9,LAMBDA(a,SUM(a)))
Q15:AD21Q15=AFILL(APP2V(APP2V(C12#,APP2H(A5:A9,APP2H(C5:N9,P5#)),,-1),C15#,,1),X8#)
B19:O23B19=APP2H(A5:A9,APP2H(C5:N9,P5#))
B27:O33B27=APP2V(APP2V(C12#,B19#,,-1),C15#,,1)
R29:AC29R29=BYCOL(R16:AC20,LAMBDA(a,AVERAGE(a)))
R31:V31R31=TRANSPOSE(BYROW(R16:AC20,LAMBDA(a,MAX(a))))
B37:O43B37=AFILL(B27#,X8#)
Q37:AD43Q37=APUZZLE(APUZZLE(APUZZLE(Q15#,R29#,7,,2,13),R31#,2,6,14,),AA32:AB33,7,1,14,1)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,813
Messages
6,181,118
Members
453,021
Latest member
Justyna P

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