ARESIZE

=ARESIZE(a,r,c)

a
array
r
new row nr.
c
new clms nr.

Resize an array

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
380
Office Version
  1. 365
Platform
  1. Windows
ARESIZE resizes an array Calls AFILL
Excel Formula:
=LAMBDA(a,r,c,AFILL(REPT("",SEQUENCE(r,c)),a))
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1sample2,12
21234=ARESIZE(A2:D7,2,12)
35678123456789101112
4910111213141516171819202122232412,2
513141516=ARESIZE(A2:D7,12,2)
6171819203,88,312
721222324=ARESIZE(A2:D7,3,8)=ARESIZE(A2:D7,8,3)34
81234567812356
991011121314151645678
101718192021222324789910
11other functions1011121112
12APP2H4,61314151314
13AFILL=ARESIZE(A2:D7,4,6)1617181516
141234561920211718
157891011122223241920
161314151617182122
171920212223242324
18
195,5
20=AFILL(ARESIZE(A2:D7,5,5),"X")=APP2H("๐Ÿ“ˆ",AFILL(ARESIZE(A2:D7,5,6),{2021,"Aug",9,"Check","this","out"}))
2112345
22678910๐Ÿ“ˆ123456
231112131415789101112
241617181920131415161718
2521222324X192021222324
262021Aug9Checkthisout
27
ARESIZE
Cell Formulas
RangeFormula
F2,F20,F13,O7,F7,T5F2=FORMULATEXT(F3)
F3:Q4F3=ARESIZE(A2:D7,2,12)
T6:U17T6=ARESIZE(A2:D7,12,2)
F8:M10F8=ARESIZE(A2:D7,3,8)
O8:Q15O8=ARESIZE(A2:D7,8,3)
F14:K17F14=ARESIZE(A2:D7,4,6)
L20L20=FORMULATEXT(M22)
F21:J25F21=AFILL(ARESIZE(A2:D7,5,5),"X")
M22:S26M22=APP2H("๐Ÿ“ˆ",AFILL(ARESIZE(A2:D7,5,6),{2021,"Aug",9,"Check","this","out"}))
Dynamic array formulas.
 
Upvote 0

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
380
Office Version
  1. 365
Platform
  1. Windows
New function, ARESIZE(a,[r],[c]), same name, same arguments (can be omitted this time), same functionality, different approach.
[r]: new rows nr. ; [c]: new clms. nr.
As much as I like the first version of the formula, came out with a longer one that does not call any other lambda and adds some new tweaks:
-if r/c omitted, array not resized, only errors/blanks replaced with null strings.
-if only one of the r/c arguments is omitted, the function calculates the smallest other argument to accommodate all elements of initial array.
Excel Formula:
=LAMBDA(a,[r],[c],
    LET(x,ROWS(a),y,COLUMNS(a),t,x*y,z,ROUNDUP(IF(r,r,IF(c,t/c,x)),0),w,ROUNDUP(IF(c,c,IF(r,t/r,y)),0),
      s,SEQUENCE(z,w),q,QUOTIENT(s-1,y)+1,m,MOD(s-1,y)+1,
      IFERROR(INDEX(IF(a="","",a),q,m),"")
    )
)
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1a,sample
2123456r,c,omittedr,3,c,8
378Aa 12=ARESIZE(A2:F5)=ARESIZE(A2:F5,3,8)
413bC171812345612345678
5192021#N/A232478Aa12Aa1213bC
613bC171817181920212324
7blank1920212324
8null string (cell F3: ="")errors/blanks replaced with null strings
9numeric string (B2:="2")text/numbers kept as they are, same size shape
10error=ISTEXT(I4)=ISNUMBER(M7)
11TRUETRUE
12check
13r,1,c,omitted <=> transpose(aflat(a))
14=ARESIZE(A2:F5,1)
1512345678Aa1213bC17181920212324
16
17r,omitted,c,1 <=> aflat(a,1)Note: If one of the r/c is omitted, the function calculates the smallest
18=ARESIZE(A2:F5,,1)other argument (integer) to accommodate all elements of initial array
191
202r,3,c,omitted (smallest c=8)
213=ARESIZE(A2:F5,3)
22412345678
235Aa1213bC
24617181920212324
257
268r,omitted,c,4 (smallest r=6)r,5,c,omitted (smallest c=5)
27A=ARESIZE(A2:F5,,4)=ARESIZE(A2:F5,5)
28a123412345
295678678Aa
3012Aa121213b
311313bCC17181920
3217181920212324
33b212324
34C
3517Only if we want other value than the smallest one,
3618we have to introduce it explicitly
3719r,5,c,6r,5,c,4
3820=ARESIZE(A2:F5,5,6)=ARESIZE(A2:F5,5,4)
39211234561234
4078Aa125678
412313bC1718Aa12
4224192021232413bC
4317181920
44initial array expanded,initial array shrinked,
45null strings added to fithas less elements than initial array
46the new dimensions
47
new ARESIZE
Cell Formulas
RangeFormula
B2B2="2"
E3E3=""
H3,N38,F38,M27,F27,F21,A18,A14,I10,L10,P3H3=FORMULATEXT(H4)
H4:M7H4=ARESIZE(A2:F5)
P4:W6P4=ARESIZE(A2:F5,3,8)
D5D5=NA()
I11I11=ISTEXT(I4)
L11L11=ISNUMBER(M7)
A15:X15A15=ARESIZE(A2:F5,1)
A19:A42A19=ARESIZE(A2:F5,,1)
F22:M24F22=ARESIZE(A2:F5,3)
F28:I33F28=ARESIZE(A2:F5,,4)
M28:Q32M28=ARESIZE(A2:F5,5)
F39:K43F39=ARESIZE(A2:F5,5,6)
N39:Q43N39=ARESIZE(A2:F5,5,4)
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
380
Office Version
  1. 365
Platform
  1. Windows
Use of ARESIZE in real life scenarios, classic unstacking.
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMN
11.) Classic unstacking
2The only thing we have to know is not total number of records,
3only the number of fields of each record, ARESIZE will do the rest
4sample
516-09-21=ARESIZE(A5:A22,,3)To filter the record with no data
6Inv. 0116-09-21Inv. 0123=AHCLEAN(C6#)
72316-09-21Inv. 023216-09-21Inv. 0123
816-09-2117-09-21Inv. 034716-09-21Inv. 0232
9Inv. 0218-09-21Inv. 041817-09-21Inv. 0347
103229-09-21Inv. 0518-09-21Inv. 0418
1117-09-2130-09-21Inv. 064730-09-21Inv. 0647
12Inv. 03
1347sample with "gaps"with gaps included, nr.fields=4
1418-09-2116-09-21=ARESIZE(C14:C36,,4)
15Inv. 04Inv. 0116-09-21Inv. 0123
16182316-09-21Inv. 0232
1729-09-2117-09-21Inv. 0347
18Inv. 0516-09-2118-09-21Inv. 0418
19#N/AInv. 0229-09-21Inv. 05
2030-09-213230-09-21Inv. 0647
21Inv. 06(we selected C14:C36, (23 rows) without extra gap of last record,
224717-09-21c was set to 4, r omitted, function knows to calculate smallest multiple of 4
23Inv. 03and returns 6 rows)
2447
25cleaning
2618-09-21=AVCLEAN(AHCLEAN(G15#,1))other functions
27Inv. 0416-09-21Inv. 0123AHCLEAN/AVCLEAN
281816-09-21Inv. 0232
2917-09-21Inv. 0347
3029-09-2118-09-21Inv. 0418
31Inv. 0530-09-21Inv. 0647
32#N/A
33single cell
3430-09-21=AVCLEAN(AHCLEAN(ARESIZE(C14:C36,,4),1))
35Inv. 0616-09-21Inv. 0123
364716-09-21Inv. 0232
3717-09-21Inv. 0347
3818-09-21Inv. 0418
3930-09-21Inv. 0647
40
new ARESIZE post 2
Cell Formulas
RangeFormula
C5,G34,G26,G14,G6C5=FORMULATEXT(C6)
C6:E11C6=ARESIZE(A5:A22,,3)
G7:I11G7=AHCLEAN(C6#)
G15:J20G15=ARESIZE(C14:C36,,4)
A19,C32A19=NA()
G27:I31G27=AVCLEAN(AHCLEAN(G15#,1))
G35:I39G35=AVCLEAN(AHCLEAN(ARESIZE(C14:C36,,4),1))
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
380
Office Version
  1. 365
Platform
  1. Windows
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMNOPQ
12.) Classic unstacking, when sample data updates, dynamic single cell formula,no refresh.
2In case that sample will update in the future with more data records, the only thing we have to do
3sample 1is to choose as array argument, a bigger range, even if it has no data yet, like A4:A43
416-09-21
5Inv. 01before cleaning will look like thisafter cleaning, single cell,
623=ARESIZE(A4:A43,,3)expands when more data is added, dynamically
716-09-2116-09-21Inv. 0123
8Inv. 0216-09-21Inv. 0232=AHCLEAN(ARESIZE(A4:A43,,3))
93217-09-21Inv. 034716-09-21Inv. 0123future error replacement with values, updates also, instantly
1017-09-2118-09-21Inv. 041816-09-21Inv. 0232sample 2
11Inv. 0318-09-21Inv. 0517-09-21Inv. 034716-09-21=AHCLEAN(ARESIZE(K11:K50,,3))
124719-09-21Inv. 064718-09-21Inv. 0418Inv. 0116-09-21Inv. 0123
1318-09-2129-09-21Inv. 075219-09-21Inv. 06472316-09-21Inv. 0232
14Inv. 0430-09-21Inv. 083029-09-21Inv. 075216-09-2117-09-21Inv. 0347
151830-09-21Inv. 0830Inv. 0218-09-21Inv. 0418
1618-09-213218-09-21Inv. 05100
17Inv. 0517-09-2119-09-21Inv. 0647
18#N/Aother functionsInv. 0323-09-21Inv. 0752
1919-09-21AHCLEAN4729-09-21Inv. 0830
20Inv. 0618-09-2130-09-21Inv. 0899
2147Inv. 04
2229-09-2118
23Inv. 07first data set18-09-21
24522nd updateInv. 05
2530-09-213rd update100
26Inv. 0819-09-21
2730Inv. 06
2847
2923-09-21
30Inv. 07
3152
3229-09-21
33Inv. 08
3430
3530-09-21
36Inv. 08
3799
38
39
40
41
42
43
44
45
46
47
48
49
50
51
new ARESIZE post 3
Cell Formulas
RangeFormula
C6,M11,G8C6=FORMULATEXT(C7)
C7:E20C7=ARESIZE(A4:A43,,3)
G9:I15G9=AHCLEAN(ARESIZE(A4:A43,,3))
M12:O20M12=AHCLEAN(ARESIZE(K11:K50,,3))
A18A18=NA()
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
380
Office Version
  1. 365
Platform
  1. Windows
ARESIZE, very handy when it comes to reshaping oversized arrays to fit certain regions of our spreadsheet's real estate, keeping the array sequential order of its elements.
AXMAS.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Task. Reshape a wide array (the ornaments array for AXMAS function) keeping the left to right unicodes values in ascending order. Doing it manually is a hassle.
2
3โฉโชโซโฌโ™”โ™•โš“โšกโšฝโ›„โœ…โœณโœดโœตโœถโœทโœธโœนโœบโœปโœผโœฝ
4๐ŸŒ๐ŸŒŽ๐ŸŒ๐ŸŒ๐ŸŒ‘๐ŸŒ’๐ŸŒ“๐ŸŒ”๐ŸŒ•๐ŸŒ–๐ŸŒ—๐ŸŒ˜๐ŸŒ™๐ŸŒš๐ŸŒ›๐ŸŒœ๐ŸŒ๐ŸŒž๐ŸŒŸ๐ŸŒ ๐ŸŒฐ๐ŸŒฑ
5๐Ÿ‰๐ŸŠ๐Ÿ‹๐ŸŒ๐Ÿ๐ŸŽ๐Ÿ๐Ÿ๐Ÿ‘๐Ÿ’๐Ÿ“๐Ÿซ๐Ÿฌ๐Ÿญ๐Ÿพ๐Ÿฟ๐ŸŽ€๐ŸŽ๐ŸŽ‚๐ŸŽƒ๐ŸŽ„๐ŸŽ…
6๐Ÿ’—๐Ÿ’˜๐Ÿ’™๐Ÿ’š๐Ÿ’›๐Ÿ’œ๐Ÿ’๐Ÿ’ž๐Ÿ’Ÿ๐Ÿ’ ๐Ÿ’ก๐Ÿ’ข๐Ÿ“๐Ÿ”๐Ÿ”Ž๐Ÿ”ฎ๐Ÿ”ฏ๐Ÿ”ด๐Ÿ”ต๐Ÿ”ถ๐Ÿ”ท๐Ÿ”ธ
7
8=UNICODE(A3:AN6)
9919391949195919698129813987598899917992499891003510036100371003810039100401004110042100431004410045
10127757127758127759127760127761127762127763127764127765127766127767127768127769127770127771127772127773127774127775127776127792127793
11127817127818127819127820127821127822127823127824127825127826127827127851127852127853127870127871127872127873127874127875127876127877
12128151128152128153128154128155128156128157128158128159128160128161128162128205128269128270128302128303128308128309128310128311128312
13
14r,omitted,c,10
15=ARESIZE(A9#,,10)=ARESIZE(A3:AN6,,10)
169193919491959196981298139875988999179924โฉโชโซโฌโ™”โ™•โš“โšกโšฝโ›„
179989100351003610037100381003910040100411004210043โœ…โœณโœดโœตโœถโœทโœธโœนโœบโœป
1810044100451004610047100481004910050100511005210053โœผโœฝโœพโœฟโ€โโ‚โƒโ„โ…
19100541005510056100571005810060100621108811093127744โ†โ‡โˆโ‰โŠโŒโŽโญโญ•๐ŸŒ€
20127757127758127759127760127761127762127763127764127765127766๐ŸŒ๐ŸŒŽ๐ŸŒ๐ŸŒ๐ŸŒ‘๐ŸŒ’๐ŸŒ“๐ŸŒ”๐ŸŒ•๐ŸŒ–
21127767127768127769127770127771127772127773127774127775127776๐ŸŒ—๐ŸŒ˜๐ŸŒ™๐ŸŒš๐ŸŒ›๐ŸŒœ๐ŸŒ๐ŸŒž๐ŸŒŸ๐ŸŒ 
22127792127793127794127799127800127801127802127803127804127805๐ŸŒฐ๐ŸŒฑ๐ŸŒฒ๐ŸŒท๐ŸŒธ๐ŸŒน๐ŸŒบ๐ŸŒป๐ŸŒผ๐ŸŒฝ
23127806127807127808127809127810127811127812127813127815127816๐ŸŒพ๐ŸŒฟ๐Ÿ€๐Ÿ๐Ÿ‚๐Ÿƒ๐Ÿ„๐Ÿ…๐Ÿ‡๐Ÿˆ
24127817127818127819127820127821127822127823127824127825127826๐Ÿ‰๐ŸŠ๐Ÿ‹๐ŸŒ๐Ÿ๐ŸŽ๐Ÿ๐Ÿ๐Ÿ‘๐Ÿ’
25127827127851127852127853127870127871127872127873127874127875๐Ÿ“๐Ÿซ๐Ÿฌ๐Ÿญ๐Ÿพ๐Ÿฟ๐ŸŽ€๐ŸŽ๐ŸŽ‚๐ŸŽƒ
26127876127877127878127879127880127881127882127883127890127936๐ŸŽ„๐ŸŽ…๐ŸŽ†๐ŸŽ‡๐ŸŽˆ๐ŸŽ‰๐ŸŽŠ๐ŸŽ‹๐ŸŽ’๐Ÿ€
27127944128030128036128037128038128039128040128125128141128142๐Ÿˆ๐Ÿž๐Ÿค๐Ÿฅ๐Ÿฆ๐Ÿง๐Ÿจ๐Ÿ‘ฝ๐Ÿ’๐Ÿ’Ž
28128151128152128153128154128155128156128157128158128159128160๐Ÿ’—๐Ÿ’˜๐Ÿ’™๐Ÿ’š๐Ÿ’›๐Ÿ’œ๐Ÿ’๐Ÿ’ž๐Ÿ’Ÿ๐Ÿ’ 
29128161128162128205128269128270128302128303128308128309128310๐Ÿ’ก๐Ÿ’ข๐Ÿ“๐Ÿ”๐Ÿ”Ž๐Ÿ”ฎ๐Ÿ”ฏ๐Ÿ”ด๐Ÿ”ต๐Ÿ”ถ
30128311128312128313128314128315128508128509128756128757128758๐Ÿ”ท๐Ÿ”ธ๐Ÿ”น๐Ÿ”บ๐Ÿ”ป๐Ÿ—ผ๐Ÿ—ฝ๐Ÿ›ด๐Ÿ›ต๐Ÿ›ถ
31128759128760128761128762128763128764129351129352129353129358๐Ÿ›ท๐Ÿ›ธ๐Ÿ›น๐Ÿ›บ๐Ÿ›ป๐Ÿ›ผ๐Ÿฅ‡๐Ÿฅˆ๐Ÿฅ‰๐ŸฅŽ
32
33
34=L16#&" "&A16#
35โฉ 9193โช 9194โซ 9195โฌ 9196โ™” 9812โ™• 9813โš“ 9875โšก 9889โšฝ 9917โ›„ 9924
36โœ… 9989โœณ 10035โœด 10036โœต 10037โœถ 10038โœท 10039โœธ 10040โœน 10041โœบ 10042โœป 10043
37โœผ 10044โœฝ 10045โœพ 10046โœฟ 10047โ€ 10048โ 10049โ‚ 10050โƒ 10051โ„ 10052โ… 10053
38โ† 10054โ‡ 10055โˆ 10056โ‰ 10057โŠ 10058โŒ 10060โŽ 10062โญ 11088โญ• 11093๐ŸŒ€ 127744
39๐ŸŒ 127757๐ŸŒŽ 127758๐ŸŒ 127759๐ŸŒ 127760๐ŸŒ‘ 127761๐ŸŒ’ 127762๐ŸŒ“ 127763๐ŸŒ” 127764๐ŸŒ• 127765๐ŸŒ– 127766
40๐ŸŒ— 127767๐ŸŒ˜ 127768๐ŸŒ™ 127769๐ŸŒš 127770๐ŸŒ› 127771๐ŸŒœ 127772๐ŸŒ 127773๐ŸŒž 127774๐ŸŒŸ 127775๐ŸŒ  127776
41๐ŸŒฐ 127792๐ŸŒฑ 127793๐ŸŒฒ 127794๐ŸŒท 127799๐ŸŒธ 127800๐ŸŒน 127801๐ŸŒบ 127802๐ŸŒป 127803๐ŸŒผ 127804๐ŸŒฝ 127805
42๐ŸŒพ 127806๐ŸŒฟ 127807๐Ÿ€ 127808๐Ÿ 127809๐Ÿ‚ 127810๐Ÿƒ 127811๐Ÿ„ 127812๐Ÿ… 127813๐Ÿ‡ 127815๐Ÿˆ 127816
43๐Ÿ‰ 127817๐ŸŠ 127818๐Ÿ‹ 127819๐ŸŒ 127820๐Ÿ 127821๐ŸŽ 127822๐Ÿ 127823๐Ÿ 127824๐Ÿ‘ 127825๐Ÿ’ 127826
44๐Ÿ“ 127827๐Ÿซ 127851๐Ÿฌ 127852๐Ÿญ 127853๐Ÿพ 127870๐Ÿฟ 127871๐ŸŽ€ 127872๐ŸŽ 127873๐ŸŽ‚ 127874๐ŸŽƒ 127875
45๐ŸŽ„ 127876๐ŸŽ… 127877๐ŸŽ† 127878๐ŸŽ‡ 127879๐ŸŽˆ 127880๐ŸŽ‰ 127881๐ŸŽŠ 127882๐ŸŽ‹ 127883๐ŸŽ’ 127890๐Ÿ€ 127936
46๐Ÿˆ 127944๐Ÿž 128030๐Ÿค 128036๐Ÿฅ 128037๐Ÿฆ 128038๐Ÿง 128039๐Ÿจ 128040๐Ÿ‘ฝ 128125๐Ÿ’ 128141๐Ÿ’Ž 128142
47๐Ÿ’— 128151๐Ÿ’˜ 128152๐Ÿ’™ 128153๐Ÿ’š 128154๐Ÿ’› 128155๐Ÿ’œ 128156๐Ÿ’ 128157๐Ÿ’ž 128158๐Ÿ’Ÿ 128159๐Ÿ’  128160
48๐Ÿ’ก 128161๐Ÿ’ข 128162๐Ÿ“ 128205๐Ÿ” 128269๐Ÿ”Ž 128270๐Ÿ”ฎ 128302๐Ÿ”ฏ 128303๐Ÿ”ด 128308๐Ÿ”ต 128309๐Ÿ”ถ 128310
49๐Ÿ”ท 128311๐Ÿ”ธ 128312๐Ÿ”น 128313๐Ÿ”บ 128314๐Ÿ”ป 128315๐Ÿ—ผ 128508๐Ÿ—ฝ 128509๐Ÿ›ด 128756๐Ÿ›ต 128757๐Ÿ›ถ 128758
50๐Ÿ›ท 128759๐Ÿ›ธ 128760๐Ÿ›น 128761๐Ÿ›บ 128762๐Ÿ›ป 128763๐Ÿ›ผ 128764๐Ÿฅ‡ 129351๐Ÿฅˆ 129352๐Ÿฅ‰ 129353๐ŸฅŽ 129358
51
ARESIZE ornaments
Cell Formulas
RangeFormula
A8,L34,L15,A15A8=FORMULATEXT(A9)
A9:AN12A9=UNICODE(A3:AN6)
A16:J31A16=ARESIZE(A9#,,10)
L16:U31L16=ARESIZE(A3:AN6,,10)
L35:U50L35=L16#&" "&A16#
Dynamic array formulas.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. Thatโ€™s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,570
Messages
5,770,913
Members
425,652
Latest member
Pemby

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
Top