ARESIZE

Xlambda · Aug 8, 2021

ARESIZE resizes an array Calls AFILL

Excel Formula:

=LAMBDA(a,r,c,AFILL(REPT("",SEQUENCE(r,c)),a))

LAMBDA 1.1.1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

sample

2,12

2

1

2

3

4

=ARESIZE(A2:D7,2,12)

3

5

6

7

8

1

2

3

4

5

6

7

8

9

10

11

12

4

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

12,2

5

13

14

15

16

=ARESIZE(A2:D7,12,2)

6

17

18

19

20

3,8

8,3

1

2

7

21

22

23

24

=ARESIZE(A2:D7,3,8)

=ARESIZE(A2:D7,8,3)

3

4

8

1

2

3

4

5

6

7

8

1

2

3

5

6

9

10

11

12

13

14

15

16

4

5

6

7

8

10

17

18

19

20

21

22

23

24

7

8

9

10

11

other functions

10

11

12

11

12

APP2H

4,6

13

14

15

13

14

13

AFILL

=ARESIZE(A2:D7,4,6)

16

17

18

15

16

14

1

2

3

4

5

6

19

20

21

17

18

15

7

8

9

10

11

12

22

23

24

19

20

16

13

14

15

16

17

18

21

22

17

19

20

21

22

23

24

23

24

18

19

5,5

20

=AFILL(ARESIZE(A2:D7,5,5),"X")

=APP2H("?",AFILL(ARESIZE(A2:D7,5,6),{2021,"Aug",9,"Check","this","out"}))

21

1

2

3

4

5

22

6

7

8

9

10

?

1

2

3

4

5

6

23

11

12

13

14

15

7

8

9

10

11

12

24

16

17

18

19

20

13

14

15

16

17

18

25

21

22

23

24

X

19

20

21

22

23

24

26

2021

Aug

9

Check

this

out

27

ARESIZE

Cell Formulas
Range	Formula
F2,F20,F13,O7,F7,T5	F2	=FORMULATEXT(F3)
F3:Q4	F3	=ARESIZE(A2:D7,2,12)
T6:U17	T6	=ARESIZE(A2:D7,12,2)
F8:M10	F8	=ARESIZE(A2:D7,3,8)
O8:Q15	O8	=ARESIZE(A2:D7,8,3)
F14:K17	F14	=ARESIZE(A2:D7,4,6)
L20	L20	=FORMULATEXT(M22)
F21:J25	F21	=AFILL(ARESIZE(A2:D7,5,5),"X")
M22:S26	M22	=APP2H("?",AFILL(ARESIZE(A2:D7,5,6),{2021,"Aug",9,"Check","this","out"}))
Dynamic array formulas.

Xlambda · Sep 18, 2021

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

1

a,sample

2

1

2

3

4

5

6

r,c,omitted

r,3,c,8

3

7

8

A

a

12

=ARESIZE(A2:F5)

=ARESIZE(A2:F5,3,8)

4

13

b

C

17

18

1

2

3

4

5

6

1

2

3

4

5

6

7

8

5

19

20

21

#N/A

23

24

7

8

A

a

12

A

a

12

13

b

C

6

13

b

C

17

18

17

18

19

20

21

23

24

7

blank

19

20

21

23

24

8

null string (cell F3: ="")

errors/blanks replaced with null strings

9

numeric string (B2:="2")

text/numbers kept as they are, same size shape

10

error

=ISTEXT(I4)

=ISNUMBER(M7)

11

TRUE

12

check

13

r,1,c,omitted <=> transpose(aflat(a))

14

=ARESIZE(A2:F5,1)

15

1

2

3

4

5

6

7

8

A

a

12

13

b

C

17

18

19

20

21

23

24

16

17

r,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

19

1

20

2

r,3,c,omitted (smallest c=8)

21

3

=ARESIZE(A2:F5,3)

22

4

1

2

3

4

5

6

7

8

23

5

A

a

12

13

b

C

24

6

17

18

19

20

21

23

24

25

7

26

8

r,omitted,c,4 (smallest r=6)

r,5,c,omitted (smallest c=5)

27

A

=ARESIZE(A2:F5,,4)

=ARESIZE(A2:F5,5)

28

a

1

2

3

4

1

2

3

4

5

29

5

6

7

8

6

7

8

A

a

30

12

A

a

12

13

b

31

13

b

C

17

18

19

20

32

17

18

19

20

21

23

24

33

b

21

23

24

34

C

35

17

Only if we want other value than the smallest one,

36

18

we have to introduce it explicitly

37

19

r,5,c,6

r,5,c,4

38

20

=ARESIZE(A2:F5,5,6)

=ARESIZE(A2:F5,5,4)

39

21

1

2

3

4

5

6

1

2

3

4

40

7

8

A

a

12

5

6

7

8

41

23

13

b

C

17

18

A

a

12

42

24

19

20

21

23

24

13

b

C

43

17

18

19

20

44

initial array expanded,

initial array shrinked,

45

null strings added to fit

has less elements than initial array

46

the new dimensions

47

new ARESIZE

Cell Formulas
Range	Formula
B2	B2	="2"
E3	E3	=""
H3,N38,F38,M27,F27,F21,A18,A14,I10,L10,P3	H3	=FORMULATEXT(H4)
H4:M7	H4	=ARESIZE(A2:F5)
P4:W6	P4	=ARESIZE(A2:F5,3,8)
D5	D5	=NA()
I11	I11	=ISTEXT(I4)
L11	L11	=ISNUMBER(M7)
A15:X15	A15	=ARESIZE(A2:F5,1)
A19:A42	A19	=ARESIZE(A2:F5,,1)
F22:M24	F22	=ARESIZE(A2:F5,3)
F28:I33	F28	=ARESIZE(A2:F5,,4)
M28:Q32	M28	=ARESIZE(A2:F5,5)
F39:K43	F39	=ARESIZE(A2:F5,5,6)
N39:Q43	N39	=ARESIZE(A2:F5,5,4)
Dynamic array formulas.

Xlambda · Sep 30, 2021

Use of ARESIZE in real life scenarios, classic unstacking.

LAMBDA 1.1.3.xlsx
A B C D E F G H I J K L M N
1 1.) Classic unstacking
2 The only thing we have to know is not total number of records,
3 only the number of fields of each record, ARESIZE will do the rest
4 sample
5 16-09-21 =ARESIZE(A5:A22,,3) To filter the record with no data
6 Inv. 01 16-09-21 Inv. 01 23 =AHCLEAN(C6#)
7 23 16-09-21 Inv. 02 32 16-09-21 Inv. 01 23
8 16-09-21 17-09-21 Inv. 03 47 16-09-21 Inv. 02 32
9 Inv. 02 18-09-21 Inv. 04 18 17-09-21 Inv. 03 47
10 32 29-09-21 Inv. 05 18-09-21 Inv. 04 18
11 17-09-21 30-09-21 Inv. 06 47 30-09-21 Inv. 06 47
12 Inv. 03
13 47 sample with "gaps" with gaps included, nr.fields=4
14 18-09-21 16-09-21 =ARESIZE(C14:C36,,4)
15 Inv. 04 Inv. 01 16-09-21 Inv. 01 23
16 18 23 16-09-21 Inv. 02 32
17 29-09-21 17-09-21 Inv. 03 47
18 Inv. 05 16-09-21 18-09-21 Inv. 04 18
19 #N/A Inv. 02 29-09-21 Inv. 05
20 30-09-21 32 30-09-21 Inv. 06 47
21 Inv. 06 (we selected C14:C36, (23 rows) without extra gap of last record,
22 47 17-09-21 c was set to 4, r omitted, function knows to calculate smallest multiple of 4
23 Inv. 03 and returns 6 rows)
24 47
25 cleaning
26 18-09-21 =AVCLEAN(AHCLEAN(G15#,1)) other functions
27 Inv. 04 16-09-21 Inv. 01 23 AHCLEAN/AVCLEAN
28 18 16-09-21 Inv. 02 32
29 17-09-21 Inv. 03 47
30 29-09-21 18-09-21 Inv. 04 18
31 Inv. 05 30-09-21 Inv. 06 47
32 #N/A
33 single cell
34 30-09-21 =AVCLEAN(AHCLEAN(ARESIZE(C14:C36,,4),1))
35 Inv. 06 16-09-21 Inv. 01 23
36 47 16-09-21 Inv. 02 32
37 17-09-21 Inv. 03 47
38 18-09-21 Inv. 04 18
39 30-09-21 Inv. 06 47
40
new ARESIZE post 2
Cell Formulas
Range Formula
C5,G34,G26,G14,G6 C5 =FORMULATEXT(C6)
C6:E11 C6 =ARESIZE(A5:A22,,3)
G7:I11 G7 =AHCLEAN(C6#)
G15:J20 G15 =ARESIZE(C14:C36,,4)
A19,C32 A19 =NA()
G27:I31 G27 =AVCLEAN(AHCLEAN(G15#,1))
G35:I39 G35 =AVCLEAN(AHCLEAN(ARESIZE(C14:C36,,4),1))
Dynamic array formulas.

Xlambda · Sep 30, 2021

LAMBDA 1.1.3.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

2.) Classic unstacking, when sample data updates, dynamic single cell formula,no refresh.

2

In case that sample will update in the future with more data records, the only thing we have to do

3

sample 1

is to choose as array argument, a bigger range, even if it has no data yet, like A4:A43

4

16-09-21

5

Inv. 01

before cleaning will look like this

after cleaning, single cell,

6

23

=ARESIZE(A4:A43,,3)

expands when more data is added, dynamically

7

16-09-21

Inv. 01

23

8

Inv. 02

16-09-21

Inv. 02

32

=AHCLEAN(ARESIZE(A4:A43,,3))

9

32

17-09-21

Inv. 03

47

16-09-21

Inv. 01

23

future error replacement with values, updates also, instantly

10

17-09-21

18-09-21

Inv. 04

18

16-09-21

Inv. 02

32

sample 2

11

Inv. 03

18-09-21

Inv. 05

17-09-21

Inv. 03

47

16-09-21

=AHCLEAN(ARESIZE(K11:K50,,3))

12

47

19-09-21

Inv. 06

47

18-09-21

Inv. 04

18

Inv. 01

16-09-21

Inv. 01

23

13

18-09-21

29-09-21

Inv. 07

52

19-09-21

Inv. 06

47

23

16-09-21

Inv. 02

32

14

Inv. 04

30-09-21

Inv. 08

30

29-09-21

Inv. 07

52

16-09-21

17-09-21

Inv. 03

47

15

18

30-09-21

Inv. 08

30

Inv. 02

18-09-21

Inv. 04

18

16

18-09-21

32

18-09-21

Inv. 05

100

17

Inv. 05

17-09-21

19-09-21

Inv. 06

47

18

#N/A

other functions

Inv. 03

23-09-21

Inv. 07

52

19

19-09-21

AHCLEAN

47

29-09-21

Inv. 08

30

20

Inv. 06

18-09-21

30-09-21

Inv. 08

99

21

47

Inv. 04

22

29-09-21

18

23

Inv. 07

first data set

18-09-21

24

52

2nd update

Inv. 05

25

30-09-21

3rd update

100

26

Inv. 08

19-09-21

27

30

Inv. 06

28

47

29

23-09-21

30

Inv. 07

31

52

32

29-09-21

33

Inv. 08

34

30

35

30-09-21

36

Inv. 08

37

99

38

39

40

41

42

43

44

45

46

47

48

49

50

51

new ARESIZE post 3

Cell Formulas
Range	Formula
C6,M11,G8	C6	=FORMULATEXT(C7)
C7:E20	C7	=ARESIZE(A4:A43,,3)
G9:I15	G9	=AHCLEAN(ARESIZE(A4:A43,,3))
M12:O20	M12	=AHCLEAN(ARESIZE(K11:K50,,3))
A18	A18	=NA()
Dynamic array formulas.

Xlambda · Dec 24, 2021

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

Task. 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)

9

9193

9194

9195

9196

9812

9813

9875

9889

9917

9924

9989

10035

10036

10037

10038

10039

10040

10041

10042

10043

10044

10045

10

127757

127758

127759

127760

127761

127762

127763

127764

127765

127766

127767

127768

127769

127770

127771

127772

127773

127774

127775

127776

127792

127793

11

127817

127818

127819

127820

127821

127822

127823

127824

127825

127826

127827

127851

127852

127853

127870

127871

127872

127873

127874

127875

127876

127877

12

128151

128152

128153

128154

128155

128156

128157

128158

128159

128160

128161

128162

128205

128269

128270

128302

128303

128308

128309

128310

128311

128312

13

14

r,omitted,c,10

15

=ARESIZE(A9#,,10)

=ARESIZE(A3:AN6,,10)

16

9193

9194

9195

9196

9812

9813

9875

9889

9917

9924

⏩

⏪

⏫

⏬

♔

♕

⚓

⚡

⚽

⛄

17

9989

10035

10036

10037

10038

10039

10040

10041

10042

10043

✅

✳

✴

✵

✶

✷

✸

✹

✺

✻

18

10044

10045

10046

10047

10048

10049

10050

10051

10052

10053

✼

✽

✾

✿

❀

❁

❂

❃

❄

❅

19

10054

10055

10056

10057

10058

10060

10062

11088

11093

127744

❆

❇

❈

❉

❊

❌

❎

⭐

⭕

?

20

127757

127758

127759

127760

127761

127762

127763

127764

127765

127766

?

21

127767

127768

127769

127770

127771

127772

127773

127774

127775

127776

?

22

127792

127793

127794

127799

127800

127801

127802

127803

127804

127805

?

23

127806

127807

127808

127809

127810

127811

127812

127813

127815

127816

?

24

127817

127818

127819

127820

127821

127822

127823

127824

127825

127826

?

25

127827

127851

127852

127853

127870

127871

127872

127873

127874

127875

?

26

127876

127877

127878

127879

127880

127881

127882

127883

127890

127936

?

27

127944

128030

128036

128037

128038

128039

128040

128125

128141

128142

?

28

128151

128152

128153

128154

128155

128156

128157

128158

128159

128160

?

29

128161

128162

128205

128269

128270

128302

128303

128308

128309

128310

?

30

128311

128312

128313

128314

128315

128508

128509

128756

128757

128758

?

31

128759

128760

128761

128762

128763

128764

129351

129352

129353

129358

?

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
Range	Formula
A8,L34,L15,A15	A8	=FORMULATEXT(A9)
A9:AN12	A9	=UNICODE(A3:AN6)
A16:J31	A16	=ARESIZE(A9#,,10)
L16:U31	L16	=ARESIZE(A3:AN6,,10)
L35:U50	L35	=L16#&" "&A16#
Dynamic array formulas.

ARESIZE

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

Cell Formulas
Range	Formula
C5,G34,G26,G14,G6	C5	=FORMULATEXT(C6)
C6:E11	C6	=ARESIZE(A5:A22,,3)
G7:I11	G7	=AHCLEAN(C6#)
G15:J20	G15	=ARESIZE(C14:C36,,4)
A19,C32	A19	=NA()
G27:I31	G27	=AVCLEAN(AHCLEAN(G15#,1))
G35:I39	G35	=AVCLEAN(AHCLEAN(ARESIZE(C14:C36,,4),1))
Dynamic array formulas.

ARESIZE

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock