ASTACK

5

13

6

w,3

sample 2

25

7

=ASTACK(A2:L4,3)

1

2

3

4

5

6

7

8

9

10

11

12

2

8

2

3

13

14

16

17

18

19

20

21

22

23

24

14

9

13

14

25

26

27

28

29

30

31

#N/A

33

34

35

36

26

10

25

26

27

3

11

4

5

6

w,5

if w<0,w,-5 w=abs(w)=5

12

16

17

18

=ASTACK(E7:P9,5)

=ASTACK(E7:P9,-5)

27

13

28

29

30

2

3

4

5

2

3

4

5

4

14

7

8

9

13

14

16

17

13

14

16

17

16

15

19

20

21

25

26

27

28

29

25

26

27

28

29

28

16

31

33

6

7

8

9

10

6

7

8

9

10

5

17

10

11

12

18

19

20

21

22

18

19

20

21

22

17

18

22

23

24

30

31

33

34

30

31

33

34

29

19

34

35

36

11

12

11

12

6

20

23

24

23

24

18

21

35

36

35

36

30

22

7

23

if abs(w)>=clms(a), w=clms(a), array will remain intact,(only errors will be replaced with null strings)

19

24

=ASTACK(A2:L4,13)

31

25

=APP2V({"Date","Doc","Sales"},C4#)

2

3

4

5

6

7

8

9

10

11

12

8

26

13

14

16

17

18

19

20

21

22

23

24

20

27

25

26

27

28

29

30

31

33

34

35

36

28

9

29

21

30

33

31

10

32

22

33

34

11

35

23

36

35

37

12

38

24

39

36

40

new ASTACK post 1

Cell Formulas
Range	Formula
R3,A24,K12,E12,A7	R3	=FORMULATEXT(R4)
H4,L9	H4	=NA()
R4:R39	R4	=ASTACK(A2:L4)
A8:C19	A8	=ASTACK(A2:L4,3)
E13:I21	E13	=ASTACK(E7:P9,5)
K13:O21	K13	=ASTACK(E7:P9,-5)
A25:L27	A25	=ASTACK(A2:L4,13)
Dynamic array formulas.

Xlambda · Aug 25, 2021

LAMBDA 1.1.2.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

sample 1

Task 1: transform sample 1 in a proper data set

or

2

08-08-21

=AOVERLAP(C4#,{"Date","Doc","Sales"},-1)

3

Invoice 1

=ASTACK(AUNSTACK(A2:A19),3)

Doc

Sales

Doc

Sales

4

30

Invoice 1

30

08-08-21

Invoice 1

30

08-08-21

Invoice 1

30

5

10-08-21

Invoice 2

15

10-08-21

Invoice 2

15

10-08-21

Invoice 2

15

6

Invoice 2

15-08-21

Invoice 3

23

15-08-21

Invoice 3

23

15-08-21

Invoice 3

23

7

15

18-08-21

Invoice 4

16

18-08-21

Invoice 4

16

18-08-21

Invoice 4

16

8

15-08-21

21-08-21

Invoice 5

18

21-08-21

Invoice 5

18

21-08-21

Invoice 5

18

9

Invoice 3

24-08-21

Invoice 6

28

24-08-21

Invoice 6

28

24-08-21

Invoice 6

28

10

23

11

18-08-21

sample 2

12

Invoice 4

08-08-21

10-08-21

15-08-21

18-08-21

21-08-21

24-08-21

13

16

Invoice 1

Invoice 2

Invoice 3

Invoice 4

Invoice 5

Invoice 6

14

21-08-21

30

15

23

16

18

28

15

Invoice 5

16

18

Task 2: Transform sample 2 in a proper data set

17

24-08-21

=TRANSPOSE(C12:H14)

18

Invoice 6

=ASTACK(AUNSTACK(ASTACK(A2:B22)),3)

Invoice 1

30

functions minisheet

19

28

10-08-21

Invoice 2

15

ASTACK

20

15-08-21

Invoice 3

23

AUNSTACK

21

18-08-21

Invoice 4

16

APP2V

22

21-08-21

Invoice 5

18

AOVERLAP

23

24-08-21

Invoice 6

28

24

stk-ustk 1

Cell Formulas
Range	Formula
G2,K2,C17,C3	G2	=FORMULATEXT(G3)
G3:I9	G3	=APP2V({"Date","Doc","Sales"},C4#)
K3:M9	K3	=AOVERLAP(C4#,{"Date","Doc","Sales"},-1)
C4:E9	C4	=ASTACK(AUNSTACK(A2:A19),3)
C18:E23	C18	=TRANSPOSE(C12:H14)
Dynamic array formulas.

Xlambda · Aug 25, 2021

LAMBDA 1.1.2.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

sample 1

Task 1: Transform sample 1 in a proper data set for invoices that already have data

2

08-08-21

19-08-21

step 1: proper data set

step 2: refining

3

Invoice 1

Invoice 7

=SORT(AHCLEAN(D4#))

4

30

21

Invoice 1

30

=TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3))

Invoice 1

30

5

10-08-21

20-08-21

10-08-21

Invoice 2

15

10-08-21

Invoice 2

15

6

Invoice 2

Invoice 8

12-08-21

Invoice 3

23

12-08-21

Invoice 3

23

7

15

12

13-08-21

Invoice 4

16

13-08-21

Invoice 4

16

8

12-08-21

21-08-21

15-08-21

Invoice 5

18

15-08-21

Invoice 5

18

9

Invoice 3

Invoice 9

18-08-21

Invoice 6

16-08-21

Invoice 13

29

10

23

19-08-21

Invoice 7

21

11

13-08-21

21-08-21

19-08-21

Invoice 7

21

20-08-21

Invoice 8

12

Invoice 4

Invoice 10

20-08-21

Invoice 8

12

21-08-21

Invoice 10

14

13

16

14

21-08-21

Invoice 9

23-08-21

Invoice 11

19

14

15-08-21

23-08-21

21-08-21

Invoice 10

14

24-08-21

Invoice 12

20

15

Invoice 5

Invoice 11

23-08-21

Invoice 11

19

16

18

19

24-08-21

Invoice 12

20

17

18-08-21

24-08-21

16-08-21

Invoice 13

29

18

Invoice 6

Invoice 12

19

20

16-08-21

21

Invoice 13

22

29

23

Task 2: Transform sample 2 in a proper data set, other method, single cell

24

sample 2

25

08-08-21

19-08-21

Invoice 1

30

26

Invoice 1

Invoice 7

10-08-21

Invoice 2

15

27

30

21

12-08-21

Invoice 3

23

28

10-08-21

20-08-21

13-08-21

Invoice 4

16

29

Invoice 2

Invoice 8

15-08-21

Invoice 5

18

=AVINSERT(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3)))),{"Date","Doc","Sales"})

Doc

Sales

30

15

12

18-08-21

Invoice 6

33

08-08-21

Invoice 1

30

31

12-08-21

21-08-21

10-08-21

Invoice 2

15

32

Invoice 3

Invoice 9

14-08-21

Invoice 14

30

12-08-21

Invoice 3

23

33

23

32

13-08-21

Invoice 4

16

34

13-08-21

21-08-21

19-08-21

Invoice 7

21

14-08-21

Invoice 14

30

35

Invoice 4

Invoice 10

20-08-21

Invoice 8

12

15-08-21

Invoice 5

18

36

16

14

21-08-21

Invoice 9

32

16-08-21

Invoice 13

29

37

15-08-21

23-08-21

21-08-21

Invoice 10

14

18-08-21

Invoice 6

33

38

Invoice 5

Invoice 11

23-08-21

Invoice 11

19

19-08-21

Invoice 7

21

39

18

19

24-08-21

Invoice 12

20

20-08-21

Invoice 8

12

40

18-08-21

24-08-21

16-08-21

Invoice 13

29

21-08-21

Invoice 9

32

41

Invoice 6

Invoice 12

21-08-21

Invoice 10

14

42

33

20

21-08-21

Invoice 15

29

21-08-21

Invoice 15

29

43

16-08-21

23-08-21

Invoice 11

19

44

Invoice 13

24-08-21

Invoice 12

20

45

29

I29:

46

14-08-21

47

Invoice 14

functions minisheet

48

30

ASTACK

49

21-08-21

AUNSTACK

50

Invoice 15

AHCLEAN

51

29

AVINSERT

52

stk-ustk 2

Cell Formulas
Range	Formula
D3,I3,D24	D3	=FORMULATEXT(D4)
D4:F17	D4	=ASTACK(AUNSTACK(ASTACK(A2:B22)),3)
I4:K14	I4	=SORT(AHCLEAN(D4#))
D25:F42	D25	=TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3))
I29:K44	I29	=AVINSERT(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3)))),{"Date","Doc","Sales"})
D45	D45	=FORMULATEXT(I29)
Dynamic array formulas.

Xlambda · Aug 25, 2021

Used same examples since both functions are used.

LAMBDA 1.1.2.xlsx
A B C D E F G H I J K L M N O P
1 sample
2 08-08-21 10-08-21 12-08-21 13-08-21 15-08-21 18-08-21 14-08-21
3 Invoice 1 Invoice 2 Invoice 3 Invoice 4 Invoice 5 Invoice 6 Invoice 14
4 30 15 23 16 18 33 30
5 19-08-21 20-08-21 21-08-21 21-08-21 23-08-21 24-08-21 16-08-21 21-08-21
6 Invoice 7 Invoice 8 Invoice 9 Invoice 10 Invoice 11 Invoice 12 Invoice 13 Invoice 15
7 21 12 32 14 19 20 29 29
8
9 Task 1: Transform sample data in a proper data set, single cell Task 2: Update report with new data
10 =SORT(AFILL(TRANSPOSE(AUNSTACK(A2:I7,3)),ASTACK(F13:H15)))
11 =AOVERLAP(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(A2:I7,3)))),{"Date","Doc","Sales"},-1) 08-08-21 Invoice 1 30
12 Date Doc Sales 09-08-21 Invoice 16 22
13 08-08-21 Invoice 1 30 New data: 09-08-21 16-08-21 25-08-21 10-08-21 Invoice 2 15
14 10-08-21 Invoice 2 15 Invoice 16 Invoice 17 Invoice 18 12-08-21 Invoice 3 23
15 12-08-21 Invoice 3 23 22 27 33 13-08-21 Invoice 4 16
16 13-08-21 Invoice 4 16 14-08-21 Invoice 14 30
17 14-08-21 Invoice 14 30 15-08-21 Invoice 5 18
18 15-08-21 Invoice 5 18 functions minisheet 16-08-21 Invoice 17 27
19 16-08-21 Invoice 13 29 ASTACK 16-08-21 Invoice 13 29
20 18-08-21 Invoice 6 33 AUNSTACK 18-08-21 Invoice 6 33
21 19-08-21 Invoice 7 21 AHCLEAN 19-08-21 Invoice 7 21
22 20-08-21 Invoice 8 12 AOVERLAP 20-08-21 Invoice 8 12
23 21-08-21 Invoice 9 32 AFILL 21-08-21 Invoice 9 32
24 21-08-21 Invoice 10 14 21-08-21 Invoice 10 14
25 21-08-21 Invoice 15 29 21-08-21 Invoice 15 29
26 23-08-21 Invoice 11 19 23-08-21 Invoice 11 19
27 24-08-21 Invoice 12 20 24-08-21 Invoice 12 20
28 25-08-21 Invoice 18 33
29
stk-ustk 3
Cell Formulas
Range Formula
J10,A11 J10 =FORMULATEXT(J11)
J11:L28 J11 =SORT(AFILL(TRANSPOSE(AUNSTACK(A2:I7,3)),ASTACK(F13:H15)))
A12:C27 A12 =AOVERLAP(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(A2:I7,3)))),{"Date","Doc","Sales"},-1)
Dynamic array formulas.

Xlambda · Aug 28, 2021

The combo ASTACK/ANSTACK is created to solve any stacking/unstacking problem with ease, as long there is a structure pattern 1D or 2D.

LAMBDA 1.1.2.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

I will try to complicate the example as much as possible with a 2D data structure.

2

Imagine that we recive data in this form factor, like a 2D card with this fields:

3

4

Date

Company

Company names: from A to D

5

Invoice nr.

Sales pers.

A1 or B3 represent sales persons of company A , respectivly B

6

Product

Amount

a1 or d4 represent products of respective companies

7

8

And we receive data in a crazy way like an unfinished puzzle:

Task: Transform in a proper sorted data set

9

Note: What is important, card is 3 by 2 and has 3*2=6 elements

10

18-08-21

A

11

Inv.01

A3

Single cell formula:

12

a6

23

=SORT(TRANSPOSE(AUNSTACK(AFLAT(AHCLEAN(ASTACK(B10:G30,2))),6)),SEQUENCE(,6))

13

25-08-21

C

03-08-21

D

03-08-21

D

Inv.46

D3

d1

12

14

Inv.23

C2

Inv.46

D3

06-08-21

A

Inv.72

A3

a8

29

15

c4

32

d1

12

06-08-21

B

Inv.58

B4

b9

29

16

15-08-21

B

15-08-21

B

Inv.18

B2

b5

19

17

Inv.18

B2

15-08-21

D

Inv.33

D7

d8

28

18

b5

19

17-08-21

A

Inv.12

A7

a1

15

19

17-08-21

A

17-08-21

C

17-08-21

C

Inv.09

C5

c3

47

20

Inv.12

A7

Inv.09

C5

18-08-21

A

Inv.01

A3

a6

23

21

a1

15

c3

47

25-08-21

C

Inv.23

C2

c4

32

22

15-08-21

D

23

Inv.33

D7

As we see, is so easy to check that data is consistent.

24

d8

28

25

06-08-21

B

To add the headers

26

Inv.58

B4

=AFILL(APP2V("",I13#),B4:C6)

27

b9

29

=XLOOKUP(SEQUENCE(4,3),SEQUENCE(4*3),N5:N16)

Company

Invoice nr.

Sales pers.

Product

Amount

28

06-08-21

A

03-08-21

D

Inv.46

D3

d1

12

29

Inv.72

A3

06-08-21

A

Inv.72

A3

a8

29

30

a8

29

06-08-21

B

Inv.58

B4

b9

29

31

15-08-21

B

Inv.18

B2

b5

19

32

15-08-21

D

Inv.33

D7

d8

28

33

functions

17-08-21

A

Inv.12

A7

a1

15

34

ASTACK

17-08-21

C

Inv.09

C5

c3

47

35

AUNSTACK

18-08-21

A

Inv.01

A3

a6

23

36

AFLAT

25-08-21

C

Inv.23

C2

c4

32

37

AHCLEAN

38

APP2V

39

AFILL

40

Sheet4

Cell Formulas
Range	Formula
I12,I26	I12	=FORMULATEXT(I13)
I13:N21	I13	=SORT(TRANSPOSE(AUNSTACK(AFLAT(AHCLEAN(ASTACK(B10:G30,2))),6)),SEQUENCE(,6))
I27:N36	I27	=AFILL(APP2V("",I13#),B4:C6)
Dynamic array formulas.

Xlambda · Sep 6, 2021

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

1

Quite unlikely to receive data as in previous post, but if we do, the combo will do the trick.

If not for subtotals versatility, most common scenarios

2

Combo was designed also, for subtotals constructions. Check

ASUBTOT

of unstacking and stacking do not even require dedicated functions,

3

This is how subtotals concept works in a basic example, step by step:

4

Task: Insert subtotals values every 3 rows

sample data that has 4 records with 3 fields each

5

4. astack and clean

Inv 01

unstacking

6

1

1. aunstack

=AHCLEAN(ASTACK(C19#))

04-09-21

7

3

=AUNSTACK(A6:A16,3)

23

Inv 01

04-09-21

23

8

4

5

6

8

3

5. append Grand Total

Inv 02

05-09-21

17

9

5

3

8

4

9

4

=APP2V(H7#,SUM(A6:A16))

05-09-21

Inv 03

05-09-21

17

10

8

4

7

6

8

=BYCOL(C8#,LAMBDA(a,SUM(a)))

17

Inv 04

06-09-21

43

11

7

5

3

Inv 03

or

12

6

8

4

05-09-21

=ARESIZE(N5:N16,4,3)

13

4

2. totals

7

8

17

Inv 01

04-09-21

23

14

6

20

5

Inv 04

Inv 02

05-09-21

17

15

8

20

16

17

6

8

06-09-21

Inv 03

05-09-21

17

16

9

4

7

43

Inv 04

06-09-21

43

17

3. appending

6

20

18

=APP2V(C8#,C15#)

16

6

and for stacking

=AFLAT(P7#)

19