AUNSTACK

2

3

10

11

12

19

20

21

28

30

4

5

6

2

3

16

18

31

32

33

4

7

8

9

4

5

6

13

14

15

22

23

24

31

32

33

7

8

9

4

5

6

19

20

21

34

35

36

5

10

11

12

7

8

9

16

18

25

26

27

34

35

36

10

11

12

7

8

9

22

23

24

6

13

14

15

13

14

15

10

11

12

25

26

27

7

16

18

if h<0, h=abs(h)

16

18

13

14

15

28

30

8

19

20

21

=AUNSTACK(A2:C13,-3)

19

20

21

9

22

23

24

2

3

10

11

12

19

20

21

28

30

22

23

24

10

25

26

27

4

5

6

13

14

15

22

23

24

31

32

33

25

26

27

11

28

#N/A

30

7

8

9

16

18

25

26

27

34

35

36

28

#N/A

30

12

31

32

33

31

32

33

13

34

35

36

34

35

36

14

15

if h,0 or omitted h=1

16

or equivalent with AUNSTACK(a)=TRANSPOSE(AFLAT(a,1))

17

=AUNSTACK(A2:C13)

18

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

18

19

20

21

22

23

24

25

26

27

28

30

31

32

33

34

35

36

19

20

if abs(h)>=rws(a), h=rows(a), array will remain intact,(only errors will be replaced with null strings)

21

=AUNSTACK(A2:C13,-20)

22

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

2

3

23

4

5

6

24

7

8

9

25

10

11

12

26

13

14

15

27

16

18

28

19

20

21

29

22

23

24

30

25

26

27

31

28

30

32

31

32

33

34

35

36

34

new AUNSTACK post 1

Cell Formulas
Range	Formula
E2,A21,A17,E8,V2	E2	=FORMULATEXT(E3)
E3:P5	E3	=AUNSTACK(A2:C13,3)
V3:AD7	V3	=AUNSTACK(R2:T13,5)
E9:P11	E9	=AUNSTACK(A2:C13,-3)
B11,S11	B11	=NA()
A18:AJ18	A18	=AUNSTACK(A2:C13)
A22:C33	A22	=AUNSTACK(A2:C13,-20)
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

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

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

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)

Invoice 1

30

12

=SORT(TRANSPOSE(AUNSTACK(ASTACK(AVCLEAN(AUNSTACK(B10:G30,3))),6)),SEQUENCE(,6))

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

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

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.
Note: Posted same example at ASTACK, but here is solved in a different way, with same outcome.
Formula used in ASTACK post: =SORT(TRANSPOSE(AUNSTACK(AFLAT(AHCLEAN(ASTACK(B10:G30,2))),6)),SEQUENCE(,6)) calls AFLAT and AHCLEAN
Formula used here : =SORT(TRANSPOSE(AUNSTACK(ASTACK(AVCLEAN(AUNSTACK(B10:G30,3))),6)),SEQUENCE(,6)) calls AVCLEAN

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

13

25-08-21

C

03-08-21

D

03-08-21

Inv.46

d1

D

D3

12

14

Inv.23

C2

Inv.46

D3

06-08-21

Inv.58

b9

B

B4

29

15

c4

32

d1

12

06-08-21

Inv.72

a8

A

A3

29

16

15-08-21

B

15-08-21

Inv.18

b5

B

B2

19

17

Inv.18

B2

15-08-21

Inv.33

d8

D

D7

28

18

b5

19

17-08-21

Inv.09

c3

C

C5

47

19

17-08-21

A

17-08-21

C

17-08-21

Inv.12

a1

A

A7

15

20

Inv.12

A7

Inv.09

C5

18-08-21

Inv.01

a6

A

A3

23

21

a1

15

c3

47

25-08-21

Inv.23

c4

C

C2

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("",F46#),B4:C6)

27

b9

29

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

functions

15-08-21

D

Inv.33

D7

d8

28

33

ASTACK

17-08-21

A

Inv.12

A7

a1

15

34

AUNSTACK

17-08-21

C

Inv.09

C5

c3

47

35

AVCLEAN

18-08-21

A

Inv.01

A3

a6

23

36

APP2V

25-08-21

C

Inv.23

C2

c4

32

37

AFILL

38

Sheet4

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

jaeiow · Feb 12, 2023

Would AUNSTACK feasibly upgrade from using MakeArray? It seems very slow on large data sets.

Xlambda · Apr 2, 2023

Sorry for replying so late. With the upcoming Easter holiday, finally, I am having some spare time to catch up.
Indeed, all lambda helper functions, as iterative algorithms, are more or less,.."slow".
Back to 2021, this function was in the phase of enthusiastic experimental mastering of lambda helper functions My iterative algorithmic awareness came later here ( ASCAN post 44-46 ).when I compared 3 of my functions, 2 iterative methods ("digital"), 1 non iterative ("analogic") and one that tboulden uses for his THUNKIT and SCANBYROWS designs that use makearray and came out to be really really slow.
That's why, using the experience we have accumulated with these functions so far, I think it is time for us to contribute, to write and adopt a Lambda Performance Act (LPA). This should include general rules and recommendations of modern lambda "programming".
A glimpse of my LPA's draft golden rules:
- Use an iterative algorithm design (lambda helper functions) ONLY when there is no other non-iterative variant possible.
- All functions (especially the ones that use new lambda helper functions) should be accompanied by a Lambda Performance Certificate (LPC).
This should contain, like any "responsible" algorithm design, info about: identifying the iterative variable (nr.rows or nr.unique values/vector…) ; the complexity of the algorithm ( Big O notation, like O(nr.rows^2) or O(3*nr.clms) ) ; speed estimation for different sample data sets sizes. Like 30s/1Mrows or instant/<=10k colums or fair/100k elements when fair means less then 30s calc time...etc ; error management level, data types spectrum etc.. (To be continued...)

To cut my revolutionary long ideas short and get back to the function, following the 1st golden rule,for AUNSTACK we can have a quite simple non-iterative, or "analogic" or "in bulk" lighting fast alternative. Positive that this function will comply any speed performance requirements

(no MAKEARRAY) same arguments, name and functionality: AUNSTACK(ar,[h])

Excel Formula:

=LAMBDA(ar, [h],
    LET(
        a, IF(ar = "", "", ar),
        x, ROWS(a),
        y, COLUMNS(a),
        k, MEDIAN(1, h, x),
        s, SEQUENCE(, ROUNDUP(x / k, 0) * y),
        q, QUOTIENT(s - 1, y),
        m, MOD(s - 1, y) + 1,
        IFERROR(INDEX(a, SEQUENCE(k) + k * q, m), "")
    )
)

Book1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

h,10 (height or new nr. of rows)

2

ar

=AUNSTACK(B3#,10)

3

2

3

4

=ASTACK(TRANSPOSE(Table1[Data]),4)

2

3

4

41

42

43

44

81

82

83

84

4

5

6

7

8

5

6

7

8

45

46

47

48

85

86

87

88

5

9

10

11

12

9

10

11

12

49

50

51

52

89

90

91

92

6

13

14

15

16

13

14

15

16

53

54

55

56

93

94

95

96

7

17

18

19

20

17

18

19

20

57

58

59

60

97

98

99

100

8

21

22

23

24

21

22

23

24

61

62

63

64

101

102

103

104

9

25

26

27

28

25

26

27

28

65

66

67

68

105

106

107

108

10

29

30

31

32

29

30

31

32

69

70

71

72

11

33

34

35

36

33

34

35

36

73

74

75

76

12

37

38

39

40

37

38

39

40

77

78

79

80

13

41

42

43

44

14

45

46

47

48

Note: As we see, AUNSTACK has nothing in common with transpose

15

49

50

51

52

16

53

54

55

56

17

57

58

59

60

18

61

62

63

64

19

65

66

67

68

20

69

70

71

72

21

73

74

75

76

22

77

78

79

80

23

81

82

83

84

24

85

86

87

88

25

89

90

91

92

26

93

94

95

96

27

97

98

99

100

28

101

102

103

104

29

105

106

107

108

30

Sheet1

Cell Formulas
Range	Formula
H2	H2	=FORMULATEXT(H3)
B3:E29	B3	=SEQUENCE(27,4)
H3:S12	H3	=AUNSTACK(B3#,10)
Dynamic array formulas.

Xlambda · Apr 17, 2023

Check this out, latest YT of PQ's master Goodly, and our lambda's alternatives.

First thing first, the ASTACK version of latest "analogic" non iterative concept used for AUNSTACK.
ASTACK(ar,[w]) (w: width)

Excel Formula:

=LAMBDA(ar, [w],
    LET(
        a, IF(ar = "", "", ar),
        x, ROWS(a),
        y, COLUMNS(a),
        k, MEDIAN(1, w, y),
        s, SEQUENCE(ROUNDUP(y / k, 0) * x),
        q, QUOTIENT(s - 1, x),
        m, MOD(s - 1, x) + 1,
        IFERROR(INDEX(a, m, SEQUENCE(, k) + k * q), "")
    )
)

Book1.xlsx

A

B

C

D

E

F

G

H

1

2

2 ways of solving

3

4

Data

Cecilia Chapman

New York

42

(257) 563-7401

5

Cecilia Chapman

Iris Watson

Hong Kong

33

(372) 587-2335

6

New York

Joanne M

Delhi

39

(786) 713-8616

7

42

Peter Highes

Delhi

39

(786) 713-8616

8

(257) 563-7401

Chandeep

Gurgaon

36

(786) 713-8616

9

Iris Watson

10

Hong Kong

=TRANSPOSE(AUNSTACK(Table1[Data],4))

11

33

Cecilia Chapman

New York

42

(257) 563-7401

12

(372) 587-2335

Iris Watson

Hong Kong

33

(372) 587-2335

13

Joanne M

Delhi

39

(786) 713-8616

14

Delhi

Peter Highes

Delhi

39

(786) 713-8616

15

39

Chandeep

Gurgaon

36

(786) 713-8616

16

(786) 713-8616

17

Peter Highes

18

Delhi

19

39

20

(786) 713-8616

21

Chandeep

22

Gurgaon

23

36

24

(786) 713-8616

25

26

Sheet2

Cell Formulas
Range	Formula
D3,D10	D3	=FORMULATEXT(D4)
D4:G8	D4	=ASTACK(TRANSPOSE(Table1[Data]),4)
D11:G15	D11	=TRANSPOSE(AUNSTACK(Table1[Data],4))
Dynamic array formulas.

Another example for ASTACK

Book1.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

Y

1

2

ar

3

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

4

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

5

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

6

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

7

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

8

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

9

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

10

11

w,7

12

=ASTACK(B3#,7)

13