"LET" Formula Help Needed

RICH937

RICH937

Board Regular
Joined
Apr 15, 2023
Messages
53
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I am using a let formula I found here (I believe) to add blank lines in a dynamic array. It's my first attempt to use the newer "Let" functionality, and while the formula here works, I cannot seem to get it to function without using hstack and a separate formula for each columns. I tried changing the number of columns in the "makearray" portion, and while it returns the 5 columns, all columns are filled with "REF" errors. Anyone know how to alter this formula so all the commands are in one LET(array,CHOOSECOLS($A3#,1),spacer,2,MAKEARRAY(ROWS(array)*spacer-(spacer-1),1,LAMBDA(r,c,IF(MOD(r-1,spacer),"",INDEX(array,ROUND((r-1)/spacer,0)+1))))) function, and no hstack is necessary?

NOTE: I went this way instead of the textjoin/textsplit route as the source data way exceeds the character limit.

thanks in advance you epic people!

Excel please help example3.xlsx
ABCDEFGHIJKL
1ARRAYSPACED ARRAY
2CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUMECATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUME
3CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825
4CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
5CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
6CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
7CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
8CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
9CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
10CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
11CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
12CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
13CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
14CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
15CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
16CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
17CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
18CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
19CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
20CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
21CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
22CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
23CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
24
25DATA SETCHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
26CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTOR VOLUME
27CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
28CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
29CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
30CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
31CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
32CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
33CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
34CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
35CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
36CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
37CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
38CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
39CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
40CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
41CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
42CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
43CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
44CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
45CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
46CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
47CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
48
Sheet2 (2)
Cell Formulas
RangeFormula
A3:E23A3=DataSet
G3:K43G3=HSTACK(LET(array,CHOOSECOLS($A3#,1),spacer,2,MAKEARRAY(ROWS(array)*spacer-(spacer-1),1,LAMBDA(r,c,IF(MOD(r-1,spacer),"",INDEX(array,ROUND((r-1)/spacer,0)+1))))),LET(array,CHOOSECOLS($A3#,2),spacer,2,MAKEARRAY(ROWS(array)*spacer-(spacer-1),1,LAMBDA(r,c,IF(MOD(r-1,spacer),"",INDEX(array,ROUND((r-1)/spacer,0)+1))))),LET(array,CHOOSECOLS($A3#,3),spacer,2,MAKEARRAY(ROWS(array)*spacer-(spacer-1),1,LAMBDA(r,c,IF(MOD(r-1,spacer),"",INDEX(array,ROUND((r-1)/spacer,0)+1))))),LET(array,CHOOSECOLS($A3#,4),spacer,2,MAKEARRAY(ROWS(array)*spacer-(spacer-1),1,LAMBDA(r,c,IF(MOD(r-1,spacer),"",INDEX(array,ROUND((r-1)/spacer,0)+1))))),LET(array,CHOOSECOLS($A3#,5),spacer,2,MAKEARRAY(ROWS(array)*spacer-(spacer-1),1,LAMBDA(r,c,IF(MOD(r-1,spacer),"",INDEX(array,ROUND((r-1)/spacer,0)+1))))))
Dynamic array formulas.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
please have a try:
工作簿1.xlsx
ABCDEFGHIJKL
1ARRAYSPACED ARRAY
2CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUMECATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUME
3CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825
4CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
5CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
6CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
7CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
8CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
9CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
10CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
11CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
12CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
13CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
14CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
15CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
16CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
17CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
18CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
19CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
20CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
21CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
22CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
23CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
24
25DATA SETCHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
26CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTOR VOLUME
27CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
28CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
29CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
30CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
31CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
32CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
33CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
34CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
35CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
36CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
37CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
38CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
39CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
40CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
41CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
42CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
43CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
44CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
45CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
46CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
47CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
48
Sheet1
Cell Formulas
RangeFormula
G2:K44G2=REDUCE(A2:E2,A3:A23,LAMBDA(x,y,VSTACK(x,OFFSET(y,,,,5),A24:E24&"")))
A3:E23A3=DataSet
Dynamic array formulas.
Named Ranges
NameRefers ToCells
DataSet=Sheet1!$A$27:$E$47A3
 
Upvote 0
or this one:
工作簿1.xlsx
ABCDEFGHIJKL
1ARRAYSPACED ARRAY
2CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUMECATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUME
3CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825
4CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
5CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
6CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
7CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
8CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
9CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
10CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
11CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
12CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
13CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
14CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
15CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
16CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
17CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
18CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
19CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
20CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
21CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
22CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
23CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
24
25DATA SETCHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
26CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTOR VOLUME
27CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
28CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
29CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
30CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
31CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
32CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
33CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
34CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
35CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
36CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
37CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
38CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
39CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
40CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
41CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
42CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
43CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
44CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
45CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
46CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
47CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
48
Sheet1
Cell Formulas
RangeFormula
A3:E23A3=DataSet
G3:K44G3=WRAPROWS(TOROW(HSTACK(A3#,IF(A3#>0,""))),5)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
DataSet=Sheet1!$A$27:$E$47A3
 
Upvote 0
shaowu459 said:
or this one:
工作簿1.xlsx
ABCDEFGHIJKL
1ARRAYSPACED ARRAY
2CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUMECATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUME
3CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825
4CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
5CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
6CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
7CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
8CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
9CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
10CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
11CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
12CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
13CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
14CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
15CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
16CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
17CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
18CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
19CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
20CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
21CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
22CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
23CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
24
25DATA SETCHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
26CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTOR VOLUME
27CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
28CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
29CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
30CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
31CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
32CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
33CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
34CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
35CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
36CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
37CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
38CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
39CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
40CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
41CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
42CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
43CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
44CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
45CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
46CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
47CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
48
Sheet1
Cell Formulas
RangeFormula
A3:E23A3=DataSet
G3:K44G3=WRAPROWS(TOROW(HSTACK(A3#,IF(A3#>0,""))),5)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
DataSet=Sheet1!$A$27:$E$47A3
Click to expand...
I don't think either of these will work, or at least I cannot figure out how to make them do so. I need 7 lines in between each line in the original array. In the crazy long Let/Lambda formula, I just typed "7" in place of "2" for the spacer. Is there a way to do this with either of these? I realize I should have mentioned that in the original post, but thought there was an easy way to recraft the original formula to simply add choosecols(a3#,1,2,3,4,5) to the Let/Lambda mix.
 
Upvote 0
Sorry, for language reasons, i may not understand your original post well. Do you need to add 7 blank lines between each row in the original array? If yes, please try this one, if not will you please post expected result and i will try to get expected result from the original array.
工作簿1.xlsx
ABCDEFGHIJKL
1ARRAYSPACED ARRAY
2CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUMECATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUME
3CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825
4CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
5CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
6CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
7CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
8CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
9CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
10CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
11CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
12CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
13CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
14CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
15CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
16CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
17CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
18CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
19CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
20CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
21CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
22CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
23CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
24
25DATA SET
26CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTOR VOLUME
27CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
28CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
29CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
30CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
31CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
32CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
33CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
34CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
35CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
36CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
37CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
38CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
39CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
40CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
41CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
42CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
43CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
44CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
45CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
46CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
47CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
48
49
50
51CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
52
53
54
55
56
57
58
59CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
60
61
62
63
64
65
66
67CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
68
69
70
71
72
73
74
75CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
76
77
78
79
80
81
82
83CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
84
85
86
87
88
89
90
91CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
92
93
94
95
96
97
98
99CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
100
101
102
103
104
105
106
107CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
108
109
110
111
112
113
114
115CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
116
117
118
119
120
121
122
123CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
124
125
126
127
128
129
130
131CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
132
133
134
135
136
137
138
139CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
140
141
142
143
144
145
146
147CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
148
149
150
151
152
153
154
155CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
156
157
158
159
160
161
162
163CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
164
165
166
167
168
169
170
171
172
173
174
175
176
Sheet1
Cell Formulas
RangeFormula
G2:K170G2=REDUCE(A2:E2,A3:A23,LAMBDA(x,y,VSTACK(x,OFFSET(y,,,,5),EXPAND("",7,5,""))))
A3:E23A3=DataSet
Dynamic array formulas.
Named Ranges
NameRefers ToCells
DataSet=Sheet1!$A$27:$E$47A3
 
Last edited:
Upvote 0
A non volatile option
Fluff.xlsm
ABCDEFGHIJK
1ARRAYSPACED ARRAY
2CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUMECATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUME
3CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825
4CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
5CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
6CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
7CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
8CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
9CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
10CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
11CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
12CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
13CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
14CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
15CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
16CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
17CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
18CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
19CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
20CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
21CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
22CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
23CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
24CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
25DATA SET
26CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTOR VOLUME
27CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
28CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
29CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
30CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
31CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
32CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
33CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
34CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
35CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
36CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
37CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
38CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
39CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
40CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
41CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
42CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
43CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
44CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
45CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
46CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
47CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
48CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
49
50
51CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
52
53
54CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
55
56
57CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
58
59
60CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
61
62
63CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
Master
Cell Formulas
RangeFormula
A3:E23A3=DataSet
G3:K63G3=LET(r,ROWS(A3#),b,2,bb,r*b-b,SORTBY(EXPAND(A3#,bb+r,,""),VSTACK(SEQUENCE(r),ROUND(SEQUENCE(bb,,,1/b),3))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
DataSet=Master!$A$27:$E$47A3
 
Upvote 0
A simpler way
Excel Formula: 
=LET(b,7,c,COLUMNS(A3#),DROP(WRAPROWS(TOCOL(EXPAND(A3#,,c*(b+1),"")),c),-b))
 
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

S
  • Solved
need dynamic array formula
2
Replies
16
Views
208
Fluff
Fluff
S
  • Question
Explain formula
Replies
4
Views
307
sohaila
S
E
  • Question
Formula for looping through two dynamic arrays at once
Replies
3
Views
677
Fluff
Fluff
P
  • Solved
single-formula combining of multiple dynamic arrays (VSTACK?)
Replies
4
Views
832
Plod518
P
M
  • Question
Proper Data set from Date Range
Replies
3
Views
178
Fluff
Fluff

Forum statistics

Threads
1,215,094
Messages
6,123,071
Members
449,092
Latest member
ipruravindra

Share this page

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