ARESIZE

=ARESIZE(a,r,c)

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

Resize an array

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
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
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.
 
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.
 
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.
 
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.
 

Forum statistics

Threads
1,216,505
Messages
6,131,026
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