Replicate data from a table n number of times and increment one of the values for each iteration

[gavcol]

Hi,

I have a data table "Tbl_Allo" with a couple of thousand records but the below table is a good example subset of the data.

In	Out	Ref	Rate	Year	Period
AAA	A1	1A	0.0001	2026	01
BBB	#	1B	0.0467	2026	01
CCC	#	1C	0.1727	2026	01
DDD	D1	1D	0.1722	2026	01
AAA	A1	1A	0.5314053	2027	01
BBB	#	1B	0.0424033	2027	01
CCC	C1	1C	0.0157139	2027	01
DDD	D1	1D	0.0000541	2027	01

What I need to achieve, preferably with a formula but happy to use power query (but not VB), is a way to recreate the table a specific n number of times (n = a variable named value "Val_Repeat" ), however I need the Period value to increment at each replication up to a maximum of 12.

I have already created a horrifically long formula (takes up nearly the whole screen) with a mixture of LET, FILTER, VSTACK and HSTACK functions but I would like a more efficient way of producing the replicated data.
Would prefer to do it in a single formula. Note, because the data contains the # it caused a problem with earlier versions of the formula that used the sequence function.

Hoping someone can suggest a way to do this with a single formula and output to a single spill dataset.
Thank you

 

[Peter_SSs]

So that we are sure what you are after, could you post the expected results for if "Val_Repeat" was, say, 3?

 

[djclements]

Not entirely clear what the expected outcome is, but here's a few examples to get you started...

Basic repeat array (vertically):

=LET(
   Val_Repeat, 6,
   CHOOSEROWS(Tbl_Allo,TOCOL(IF(SEQUENCE(Val_Repeat),SEQUENCE(,ROWS(Tbl_Allo)))))
)

Repeat array, with appended instance_num column:

=LET(
   Val_Repeat, 6,
   x, SEQUENCE(,ROWS(Tbl_Allo)),
   y, SEQUENCE(Val_Repeat),
   HSTACK(CHOOSEROWS(Tbl_Allo,TOCOL(IF(y,x))),TOCOL(IF(x,y)))
)

Repeat array, then add the instance_num to the existing Period column (last column):

=LET(
   Val_Repeat, 6,
   x, SEQUENCE(,ROWS(Tbl_Allo)),
   y, SEQUENCE(Val_Repeat),
   a, CHOOSEROWS(Tbl_Allo,TOCOL(IF(y,x))),
   HSTACK(DROP(a,,-1),TAKE(a,,-1)+TOCOL(IF(x,y)))
)

Repeat array, then add the instance_num (starting from 0) to the Period column:

=LET(
   Val_Repeat, 6,
   x, SEQUENCE(,ROWS(Tbl_Allo)),
   y, SEQUENCE(Val_Repeat,,0),
   a, CHOOSEROWS(Tbl_Allo,TOCOL(IF({1},x,y))),
   HSTACK(DROP(a,,-1),TAKE(a,,-1)+TOCOL(IF(x,y)))
)

There are a multitude of variations this method can take, depending on your desired output. If none of these examples meet your needs, please provide some sample results, as mentioned by Peter.

 

[saboh12617]

Repeat array, then add the instance_num (starting from 0) to the Period column:

=LET(
   Val_Repeat, 6,
   x, SEQUENCE(,ROWS(Tbl_Allo)),
   y, SEQUENCE(Val_Repeat,,0),
   a, CHOOSEROWS(Tbl_Allo,TOCOL(IF({1},x,y))),
   HSTACK(DROP(a,,-1),TAKE(a,,-1)+TOCOL(IF(x,y)))
)

There are a multitude of variations this method can take, depending on your desired output. If none of these examples meet your needs, please provide some sample results, as mentioned by Peter.

Hello,

Just out of curiosity, what does this part means in your last formula?

IF({1},x,y)

I struggle to understand when you'd reach y, and also why the brackets ?

Tyvm

 

[djclements]

@saboh12617

In the 2nd and 3rd examples, y was defined as SEQUENCE(6), returning a vertical vector from 1 to 6. Since the IF function interprets any numeric value other than 0 as TRUE, IF(y,x) was all that was needed to broadcast (repeat) the horizontal x vector across all 6 rows of the y vector.

In the last example, however, y was defined as SEQUENCE(6,,0), returning a vertical vector from 0 to 5. Since 0 is interpreted as FALSE, the same IF(y,x) construct will not work correctly. Instead, IF({1},x,y) is used to successfully broadcast the horizontal x vector across each row of the y vector. Whenever the logical_test argument of IF receives an array object (TYPE 64), all three arguments are resized to be the same length/width as the argument(s) with the greatest number of rows and columns.

 

[saboh12617]

Ohhh that's a very clever way to duplicate an array indeed!
Do you have a link where I could learn more about the interactions between Excel Formulas and types casting/inferences – Especially for arrays? It is the first time I see it explained in those technical terms, I think it can help me understanding and working with arrays better.

Thanks for your time sir, have a good day.

 

[gavcol]

So that we are sure what you are after, could you post the expected results for if "Val_Repeat" was, say, 3?

Hi Peter, Thanks for the response.

I may have given a bum steer in the original post. I don't need it by n val_repeat because the source data will already include the first period of data in each year which needs to be replicated to the other 11 periods.

I hope this expected output explains it better.

Replicate dataset by 12 for each year.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1								Source Data table (period 1 for each year is provided, but need 12 periods replicated for each year which has different rates in period 1 for each year)
2
3	Expected Output = Replicated data (x 12 periods for each RepYear)
4	In	Out	Ref	Rate	Year	Period			In	Out	Ref	Rate	RepYear	Period
5	AAA	A1	1A	0.00001	2026	1			AAA	A1	1A	0.00001	2026	1	Subset #1 replicate by 12 periods for 2026
6	BBB	#	1B	0.00002	2026	1			BBB	#	1B	0.00002	2026	1
7	CCC	#	1C	0.00003	2026	1			CCC	#	1C	0.00003	2026	1
8	DDD	D1	1D	0.00004	2026	1			DDD	D1	1D	0.00004	2026	1
9	AAA	A1	1A	0.00001	2026	2			AAA	A1	1A	0.005	2027	1	Subset #2 replicate by 12 periods for 2027
10	BBB	#	1B	0.00002	2026	2			BBB	#	1B	0.006	2027	1
11	CCC	#	1C	0.00003	2026	2			CCC	C1	1C	0.007	2027	1
12	DDD	D1	1D	0.00004	2026	2			DDD	D1	1D	0.008	2027	1
13	AAA	A1	1A	0.00001	2026	3			AAA	A1	1A	0.1	2028	1	Subset #3 replicate by 12 periods for 2028
14	BBB	#	1B	0.00002	2026	3			BBB	#	1B	0.3	2028	1
15	CCC	#	1C	0.00003	2026	3			CCC	C1	1C	0.6	2028	1
16	DDD	D1	1D	0.00004	2026	3			DDD	D1	1D	0.9	2028	1
17	AAA	A1	1A	0.00001	2026	4
18	BBB	#	1B	0.00002	2026	4
19	CCC	#	1C	0.00003	2026	4
20	DDD	D1	1D	0.00004	2026	4
21	AAA	A1	1A	0.00001	2026	5
22	BBB	#	1B	0.00002	2026	5
23	CCC	#	1C	0.00003	2026	5
24	DDD	D1	1D	0.00004	2026	5
25	AAA	A1	1A	0.00001	2026	6
26	BBB	#	1B	0.00002	2026	6
27	CCC	#	1C	0.00003	2026	6
28	DDD	D1	1D	0.00004	2026	6
29	AAA	A1	1A	0.00001	2026	7
30	BBB	#	1B	0.00002	2026	7
31	CCC	#	1C	0.00003	2026	7
32	DDD	D1	1D	0.00004	2026	7
33	AAA	A1	1A	0.00001	2026	8
34	BBB	#	1B	0.00002	2026	8
35	CCC	#	1C	0.00003	2026	8
36	DDD	D1	1D	0.00004	2026	8
37	AAA	A1	1A	0.00001	2026	9
38	BBB	#	1B	0.00002	2026	9
39	CCC	#	1C	0.00003	2026	9
40	DDD	D1	1D	0.00004	2026	9
41	AAA	A1	1A	0.00001	2026	10
42	BBB	#	1B	0.00002	2026	10
43	CCC	#	1C	0.00003	2026	10
44	DDD	D1	1D	0.00004	2026	10
45	AAA	A1	1A	0.00001	2026	11
46	BBB	#	1B	0.00002	2026	11
47	CCC	#	1C	0.00003	2026	11
48	DDD	D1	1D	0.00004	2026	11
49	AAA	A1	1A	0.00001	2026	12
50	BBB	#	1B	0.00002	2026	12
51	CCC	#	1C	0.00003	2026	12
52	DDD	D1	1D	0.00004	2026	12
53	AAA	A1	1A	0.005	2027	1
54	BBB	#	1B	0.006	2027	1
55	CCC	#	1C	0.007	2027	1
56	DDD	D1	1D	0.008	2027	1
57	AAA	A1	1A	0.005	2027	2
58	BBB	#	1B	0.006	2027	2
59	CCC	#	1C	0.007	2027	2
60	DDD	D1	1D	0.008	2027	2
61	AAA	A1	1A	0.005	2027	3
62	BBB	#	1B	0.006	2027	3
63	CCC	#	1C	0.007	2027	3
64	DDD	D1	1D	0.008	2027	3
65	AAA	A1	1A	0.005	2027	4
66	BBB	#	1B	0.006	2027	4
67	CCC	#	1C	0.007	2027	4
68	DDD	D1	1D	0.008	2027	4
69	AAA	A1	1A	0.005	2027	5
70	BBB	#	1B	0.006	2027	5
71	CCC	#	1C	0.007	2027	5
72	DDD	D1	1D	0.008	2027	5
73	AAA	A1	1A	0.005	2027	6
74	BBB	#	1B	0.006	2027	6
75	CCC	#	1C	0.007	2027	6
76	DDD	D1	1D	0.008	2027	6
77	AAA	A1	1A	0.005	2027	7
78	BBB	#	1B	0.006	2027	7
79	CCC	#	1C	0.007	2027	7
80	DDD	D1	1D	0.008	2027	7
81	AAA	A1	1A	0.005	2027	8
82	BBB	#	1B	0.006	2027	8
83	CCC	#	1C	0.007	2027	8
84	DDD	D1	1D	0.008	2027	8
85	AAA	A1	1A	0.005	2027	9
86	BBB	#	1B	0.006	2027	9
87	CCC	#	1C	0.007	2027	9
88	DDD	D1	1D	0.008	2027	9
89	AAA	A1	1A	0.005	2027	10
90	BBB	#	1B	0.006	2027	10
91	CCC	#	1C	0.007	2027	10
92	DDD	D1	1D	0.008	2027	10
93	AAA	A1	1A	0.005	2027	11
94	BBB	#	1B	0.006	2027	11
95	CCC	#	1C	0.007	2027	11
96	DDD	D1	1D	0.008	2027	11
97	AAA	A1	1A	0.005	2027	12
98	BBB	#	1B	0.006	2027	12
99	CCC	#	1C	0.007	2027	12
100	DDD	D1	1D	0.008	2027	12
101	AAA	A1	1A	0.1	2028	1
102	BBB	#	1B	0.3	2028	1
103	CCC	#	1C	0.6	2028	1
104	DDD	D1	1D	0.9	2028	1
105	AAA	A1	1A	0.1	2028	2
106	BBB	#	1B	0.3	2028	2
107	CCC	#	1C	0.6	2028	2
108	DDD	D1	1D	0.9	2028	2
109	AAA	A1	1A	0.1	2028	3
110	BBB	#	1B	0.3	2028	3
111	CCC	#	1C	0.6	2028	3
112	DDD	D1	1D	0.9	2028	3
113	AAA	A1	1A	0.1	2028	4
114	BBB	#	1B	0.3	2028	4
115	CCC	#	1C	0.6	2028	4
116	DDD	D1	1D	0.9	2028	4
117	AAA	A1	1A	0.1	2028	5
118	BBB	#	1B	0.3	2028	5
119	CCC	#	1C	0.6	2028	5
120	DDD	D1	1D	0.9	2028	5
121	AAA	A1	1A	0.1	2028	6
122	BBB	#	1B	0.3	2028	6
123	CCC	#	1C	0.6	2028	6
124	DDD	D1	1D	0.9	2028	6
125	AAA	A1	1A	0.1	2028	7
126	BBB	#	1B	0.3	2028	7
127	CCC	#	1C	0.6	2028	7
128	DDD	D1	1D	0.9	2028	7
129	AAA	A1	1A	0.1	2028	8
130	BBB	#	1B	0.3	2028	8
131	CCC	#	1C	0.6	2028	8
132	DDD	D1	1D	0.9	2028	8
133	AAA	A1	1A	0.1	2028	9
134	BBB	#	1B	0.3	2028	9
135	CCC	#	1C	0.6	2028	9
136	DDD	D1	1D	0.9	2028	9
137	AAA	A1	1A	0.1	2028	10
138	BBB	#	1B	0.3	2028	10
139	CCC	#	1C	0.6	2028	10
140	DDD	D1	1D	0.9	2028	10
141	AAA	A1	1A	0.1	2028	11
142	BBB	#	1B	0.3	2028	11
143	CCC	#	1C	0.6	2028	11
144	DDD	D1	1D	0.9	2028	11
145	AAA	A1	1A	0.1	2028	12
146	BBB	#	1B	0.3	2028	12
147	CCC	#	1C	0.6	2028	12
148	DDD	D1	1D	0.9	2028	12
Source

 

[gavcol]

Hi Peter, Thanks for the response.

I may have given a bum steer in the original post. I don't need it by n val_repeat because the source data will already include the first period of data in each year which needs to be replicated to the other 11 periods.

I hope this expected output explains it better.

Replicate dataset by 12 for each year.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1								Source Data table (period 1 for each year is provided, but need 12 periods replicated for each year which has different rates in period 1 for each year)
2
3	Expected Output = Replicated data (x 12 periods for each RepYear)
4	In	Out	Ref	Rate	Year	Period			In	Out	Ref	Rate	RepYear	Period
5	AAA	A1	1A	0.00001	2026	1			AAA	A1	1A	0.00001	2026	1	Subset #1 replicate by 12 periods for 2026
6	BBB	#	1B	0.00002	2026	1			BBB	#	1B	0.00002	2026	1
7	CCC	#	1C	0.00003	2026	1			CCC	#	1C	0.00003	2026	1
8	DDD	D1	1D	0.00004	2026	1			DDD	D1	1D	0.00004	2026	1
9	AAA	A1	1A	0.00001	2026	2			AAA	A1	1A	0.005	2027	1	Subset #2 replicate by 12 periods for 2027
10	BBB	#	1B	0.00002	2026	2			BBB	#	1B	0.006	2027	1
11	CCC	#	1C	0.00003	2026	2			CCC	C1	1C	0.007	2027	1
12	DDD	D1	1D	0.00004	2026	2			DDD	D1	1D	0.008	2027	1
13	AAA	A1	1A	0.00001	2026	3			AAA	A1	1A	0.1	2028	1	Subset #3 replicate by 12 periods for 2028
14	BBB	#	1B	0.00002	2026	3			BBB	#	1B	0.3	2028	1
15	CCC	#	1C	0.00003	2026	3			CCC	C1	1C	0.6	2028	1
16	DDD	D1	1D	0.00004	2026	3			DDD	D1	1D	0.9	2028	1
17	AAA	A1	1A	0.00001	2026	4
18	BBB	#	1B	0.00002	2026	4
19	CCC	#	1C	0.00003	2026	4
20	DDD	D1	1D	0.00004	2026	4
21	AAA	A1	1A	0.00001	2026	5
22	BBB	#	1B	0.00002	2026	5
23	CCC	#	1C	0.00003	2026	5
24	DDD	D1	1D	0.00004	2026	5
25	AAA	A1	1A	0.00001	2026	6
26	BBB	#	1B	0.00002	2026	6
27	CCC	#	1C	0.00003	2026	6
28	DDD	D1	1D	0.00004	2026	6
29	AAA	A1	1A	0.00001	2026	7
30	BBB	#	1B	0.00002	2026	7
31	CCC	#	1C	0.00003	2026	7
32	DDD	D1	1D	0.00004	2026	7
33	AAA	A1	1A	0.00001	2026	8
34	BBB	#	1B	0.00002	2026	8
35	CCC	#	1C	0.00003	2026	8
36	DDD	D1	1D	0.00004	2026	8
37	AAA	A1	1A	0.00001	2026	9
38	BBB	#	1B	0.00002	2026	9
39	CCC	#	1C	0.00003	2026	9
40	DDD	D1	1D	0.00004	2026	9
41	AAA	A1	1A	0.00001	2026	10
42	BBB	#	1B	0.00002	2026	10
43	CCC	#	1C	0.00003	2026	10
44	DDD	D1	1D	0.00004	2026	10
45	AAA	A1	1A	0.00001	2026	11
46	BBB	#	1B	0.00002	2026	11
47	CCC	#	1C	0.00003	2026	11
48	DDD	D1	1D	0.00004	2026	11
49	AAA	A1	1A	0.00001	2026	12
50	BBB	#	1B	0.00002	2026	12
51	CCC	#	1C	0.00003	2026	12
52	DDD	D1	1D	0.00004	2026	12
53	AAA	A1	1A	0.005	2027	1
54	BBB	#	1B	0.006	2027	1
55	CCC	#	1C	0.007	2027	1
56	DDD	D1	1D	0.008	2027	1
57	AAA	A1	1A	0.005	2027	2
58	BBB	#	1B	0.006	2027	2
59	CCC	#	1C	0.007	2027	2
60	DDD	D1	1D	0.008	2027	2
61	AAA	A1	1A	0.005	2027	3
62	BBB	#	1B	0.006	2027	3
63	CCC	#	1C	0.007	2027	3
64	DDD	D1	1D	0.008	2027	3
65	AAA	A1	1A	0.005	2027	4
66	BBB	#	1B	0.006	2027	4
67	CCC	#	1C	0.007	2027	4
68	DDD	D1	1D	0.008	2027	4
69	AAA	A1	1A	0.005	2027	5
70	BBB	#	1B	0.006	2027	5
71	CCC	#	1C	0.007	2027	5
72	DDD	D1	1D	0.008	2027	5
73	AAA	A1	1A	0.005	2027	6
74	BBB	#	1B	0.006	2027	6
75	CCC	#	1C	0.007	2027	6
76	DDD	D1	1D	0.008	2027	6
77	AAA	A1	1A	0.005	2027	7
78	BBB	#	1B	0.006	2027	7
79	CCC	#	1C	0.007	2027	7
80	DDD	D1	1D	0.008	2027	7
81	AAA	A1	1A	0.005	2027	8
82	BBB	#	1B	0.006	2027	8
83	CCC	#	1C	0.007	2027	8
84	DDD	D1	1D	0.008	2027	8
85	AAA	A1	1A	0.005	2027	9
86	BBB	#	1B	0.006	2027	9
87	CCC	#	1C	0.007	2027	9
88	DDD	D1	1D	0.008	2027	9
89	AAA	A1	1A	0.005	2027	10
90	BBB	#	1B	0.006	2027	10
91	CCC	#	1C	0.007	2027	10
92	DDD	D1	1D	0.008	2027	10
93	AAA	A1	1A	0.005	2027	11
94	BBB	#	1B	0.006	2027	11
95	CCC	#	1C	0.007	2027	11
96	DDD	D1	1D	0.008	2027	11
97	AAA	A1	1A	0.005	2027	12
98	BBB	#	1B	0.006	2027	12
99	CCC	#	1C	0.007	2027	12
100	DDD	D1	1D	0.008	2027	12
101	AAA	A1	1A	0.1	2028	1
102	BBB	#	1B	0.3	2028	1
103	CCC	#	1C	0.6	2028	1
104	DDD	D1	1D	0.9	2028	1
105	AAA	A1	1A	0.1	2028	2
106	BBB	#	1B	0.3	2028	2
107	CCC	#	1C	0.6	2028	2
108	DDD	D1	1D	0.9	2028	2
109	AAA	A1	1A	0.1	2028	3
110	BBB	#	1B	0.3	2028	3
111	CCC	#	1C	0.6	2028	3
112	DDD	D1	1D	0.9	2028	3
113	AAA	A1	1A	0.1	2028	4
114	BBB	#	1B	0.3	2028	4
115	CCC	#	1C	0.6	2028	4
116	DDD	D1	1D	0.9	2028	4
117	AAA	A1	1A	0.1	2028	5
118	BBB	#	1B	0.3	2028	5
119	CCC	#	1C	0.6	2028	5
120	DDD	D1	1D	0.9	2028	5
121	AAA	A1	1A	0.1	2028	6
122	BBB	#	1B	0.3	2028	6
123	CCC	#	1C	0.6	2028	6
124	DDD	D1	1D	0.9	2028	6
125	AAA	A1	1A	0.1	2028	7
126	BBB	#	1B	0.3	2028	7
127	CCC	#	1C	0.6	2028	7
128	DDD	D1	1D	0.9	2028	7
129	AAA	A1	1A	0.1	2028	8
130	BBB	#	1B	0.3	2028	8
131	CCC	#	1C	0.6	2028	8
132	DDD	D1	1D	0.9	2028	8
133	AAA	A1	1A	0.1	2028	9
134	BBB	#	1B	0.3	2028	9
135	CCC	#	1C	0.6	2028	9
136	DDD	D1	1D	0.9	2028	9
137	AAA	A1	1A	0.1	2028	10
138	BBB	#	1B	0.3	2028	10
139	CCC	#	1C	0.6	2028	10
140	DDD	D1	1D	0.9	2028	10
141	AAA	A1	1A	0.1	2028	11
142	BBB	#	1B	0.3	2028	11
143	CCC	#	1C	0.6	2028	11
144	DDD	D1	1D	0.9	2028	11
145	AAA	A1	1A	0.1	2028	12
146	BBB	#	1B	0.3	2028	12
147	CCC	#	1C	0.6	2028	12
148	DDD	D1	1D	0.9	2028	12
Source

So the request should have been to replicate the data for each RepYear and Period by 11 and increment the Period by 1 for each replication.

 

[gavcol]

Not entirely clear what the expected outcome is, but here's a few examples to get you started...
There are a multitude of variations this method can take, depending on your desired output. If none of these examples meet your needs, please provide some sample results, as mentioned by Peter.

@djclements - Holy dooley that last one did it perfectly once I changed the Val_Repeat to12 !!!
I don't know why my earlier formula with SEQUENCE had such a problem with the # values and I had played around with TOCOL but I never even thought of using the drop and take functions !!
I am properly amazed at this.
Thank you muchly. This is exactly what I needed !!! (It's a different sort sequence to my example above but that doesn't matter at all. The production of the records was all that was required)

 

[gavcol]

and just for some icing, a little update to sort..............
(though I adjusted the example rates a bit to ensure the sort didn't re sort by their values)

=SORT(LET(
   Val_Repeat, 12,
   x, SEQUENCE(,ROWS(Tbl_Allo)),
   y, SEQUENCE(Val_Repeat,,0),
   a, CHOOSEROWS(Tbl_Allo,TOCOL(IF({1},x,y))),
   HSTACK(DROP(a,,-1),TAKE(a,,-1)+TOCOL(IF(x,y)))
),5)

.............the output then results in something I can give to end users in a way they'll understand the data

Replicate dataset by 12 for each year.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	In	Out	Ref	Rate	Year	Period			Source Data table (period 1 for each year is provided, but need 12 periods replicated for each year which has different rates in period 1 for each year)
2	AAA	A1	1A	0.00009	2026	1
3	BBB	#	1B	0.00008	2026	1
4	CCC	#	1C	0.00007	2026	1				In	Out	Ref	Rate	RepYear	Period
5	DDD	D1	1D	0.00006	2026	1				AAA	A1	1A	0.00009	2026	1	Subset #1 replicate by 12 periods for 2026
6	AAA	A1	1A	0.00009	2026	2				BBB	#	1B	0.00008	2026	1
7	BBB	#	1B	0.00008	2026	2				CCC	#	1C	0.00007	2026	1
8	CCC	#	1C	0.00007	2026	2				DDD	D1	1D	0.00006	2026	1
9	DDD	D1	1D	0.00006	2026	2				AAA	A1	1A	0.008	2027	1	Subset #2 replicate by 12 periods for 2027
10	AAA	A1	1A	0.00009	2026	3				BBB	#	1B	0.006	2027	1
11	BBB	#	1B	0.00008	2026	3				CCC	C1	1C	0.007	2027	1
12	CCC	#	1C	0.00007	2026	3				DDD	D1	1D	0.005	2027	1
13	DDD	D1	1D	0.00006	2026	3				AAA	A1	1A	0.1	2028	1	Subset #3 replicate by 12 periods for 2028
14	AAA	A1	1A	0.00009	2026	4				BBB	#	1B	0.3	2028	1
15	BBB	#	1B	0.00008	2026	4				CCC	C1	1C	0.6	2028	1
16	CCC	#	1C	0.00007	2026	4				DDD	D1	1D	0.9	2028	1
17	DDD	D1	1D	0.00006	2026	4
18	AAA	A1	1A	0.00009	2026	5
19	BBB	#	1B	0.00008	2026	5
20	CCC	#	1C	0.00007	2026	5
21	DDD	D1	1D	0.00006	2026	5
22	AAA	A1	1A	0.00009	2026	6
23	BBB	#	1B	0.00008	2026	6
24	CCC	#	1C	0.00007	2026	6
25	DDD	D1	1D	0.00006	2026	6
26	AAA	A1	1A	0.00009	2026	7
27	BBB	#	1B	0.00008	2026	7
28	CCC	#	1C	0.00007	2026	7
29	DDD	D1	1D	0.00006	2026	7
30	AAA	A1	1A	0.00009	2026	8
31	BBB	#	1B	0.00008	2026	8
32	CCC	#	1C	0.00007	2026	8
33	DDD	D1	1D	0.00006	2026	8
34	AAA	A1	1A	0.00009	2026	9
35	BBB	#	1B	0.00008	2026	9
36	CCC	#	1C	0.00007	2026	9
37	DDD	D1	1D	0.00006	2026	9
38	AAA	A1	1A	0.00009	2026	10
39	BBB	#	1B	0.00008	2026	10
40	CCC	#	1C	0.00007	2026	10
41	DDD	D1	1D	0.00006	2026	10
42	AAA	A1	1A	0.00009	2026	11
43	BBB	#	1B	0.00008	2026	11
44	CCC	#	1C	0.00007	2026	11
45	DDD	D1	1D	0.00006	2026	11
46	AAA	A1	1A	0.00009	2026	12
47	BBB	#	1B	0.00008	2026	12
48	CCC	#	1C	0.00007	2026	12
49	DDD	D1	1D	0.00006	2026	12
50	AAA	A1	1A	0.008	2027	1
51	BBB	#	1B	0.006	2027	1
52	CCC	C1	1C	0.007	2027	1
53	DDD	D1	1D	0.005	2027	1
54	AAA	A1	1A	0.008	2027	2
55	BBB	#	1B	0.006	2027	2
56	CCC	C1	1C	0.007	2027	2
57	DDD	D1	1D	0.005	2027	2
58	AAA	A1	1A	0.008	2027	3
59	BBB	#	1B	0.006	2027	3
60	CCC	C1	1C	0.007	2027	3
61	DDD	D1	1D	0.005	2027	3
62	AAA	A1	1A	0.008	2027	4
63	BBB	#	1B	0.006	2027	4
64	CCC	C1	1C	0.007	2027	4
65	DDD	D1	1D	0.005	2027	4
66	AAA	A1	1A	0.008	2027	5
67	BBB	#	1B	0.006	2027	5
68	CCC	C1	1C	0.007	2027	5
69	DDD	D1	1D	0.005	2027	5
70	AAA	A1	1A	0.008	2027	6
71	BBB	#	1B	0.006	2027	6
72	CCC	C1	1C	0.007	2027	6
73	DDD	D1	1D	0.005	2027	6
74	AAA	A1	1A	0.008	2027	7
75	BBB	#	1B	0.006	2027	7
76	CCC	C1	1C	0.007	2027	7
77	DDD	D1	1D	0.005	2027	7
78	AAA	A1	1A	0.008	2027	8
79	BBB	#	1B	0.006	2027	8
80	CCC	C1	1C	0.007	2027	8
81	DDD	D1	1D	0.005	2027	8
82	AAA	A1	1A	0.008	2027	9
83	BBB	#	1B	0.006	2027	9
84	CCC	C1	1C	0.007	2027	9
85	DDD	D1	1D	0.005	2027	9
86	AAA	A1	1A	0.008	2027	10
87	BBB	#	1B	0.006	2027	10
88	CCC	C1	1C	0.007	2027	10
89	DDD	D1	1D	0.005	2027	10
90	AAA	A1	1A	0.008	2027	11
91	BBB	#	1B	0.006	2027	11
92	CCC	C1	1C	0.007	2027	11
93	DDD	D1	1D	0.005	2027	11
94	AAA	A1	1A	0.008	2027	12
95	BBB	#	1B	0.006	2027	12
96	CCC	C1	1C	0.007	2027	12
97	DDD	D1	1D	0.005	2027	12
98	AAA	A1	1A	0.1	2028	1
99	BBB	#	1B	0.3	2028	1
100	CCC	C1	1C	0.6	2028	1
101	DDD	D1	1D	0.9	2028	1
102	AAA	A1	1A	0.1	2028	2
103	BBB	#	1B	0.3	2028	2
104	CCC	C1	1C	0.6	2028	2
105	DDD	D1	1D	0.9	2028	2
106	AAA	A1	1A	0.1	2028	3
107	BBB	#	1B	0.3	2028	3
108	CCC	C1	1C	0.6	2028	3
109	DDD	D1	1D	0.9	2028	3
110	AAA	A1	1A	0.1	2028	4
111	BBB	#	1B	0.3	2028	4
112	CCC	C1	1C	0.6	2028	4
113	DDD	D1	1D	0.9	2028	4
114	AAA	A1	1A	0.1	2028	5
115	BBB	#	1B	0.3	2028	5
116	CCC	C1	1C	0.6	2028	5
117	DDD	D1	1D	0.9	2028	5
118	AAA	A1	1A	0.1	2028	6
119	BBB	#	1B	0.3	2028	6
120	CCC	C1	1C	0.6	2028	6
121	DDD	D1	1D	0.9	2028	6
122	AAA	A1	1A	0.1	2028	7
123	BBB	#	1B	0.3	2028	7
124	CCC	C1	1C	0.6	2028	7
125	DDD	D1	1D	0.9	2028	7
126	AAA	A1	1A	0.1	2028	8
127	BBB	#	1B	0.3	2028	8
128	CCC	C1	1C	0.6	2028	8
129	DDD	D1	1D	0.9	2028	8
130	AAA	A1	1A	0.1	2028	9
131	BBB	#	1B	0.3	2028	9
132	CCC	C1	1C	0.6	2028	9
133	DDD	D1	1D	0.9	2028	9
134	AAA	A1	1A	0.1	2028	10
135	BBB	#	1B	0.3	2028	10
136	CCC	C1	1C	0.6	2028	10
137	DDD	D1	1D	0.9	2028	10
138	AAA	A1	1A	0.1	2028	11
139	BBB	#	1B	0.3	2028	11
140	CCC	C1	1C	0.6	2028	11
141	DDD	D1	1D	0.9	2028	11
142	AAA	A1	1A	0.1	2028	12
143	BBB	#	1B	0.3	2028	12
144	CCC	C1	1C	0.6	2028	12
145	DDD	D1	1D	0.9	2028	12
Outcome

Cell Formulas
Range	Formula
A2:F145	A2	=SORT(LET( Val_Repeat, 12, x, SEQUENCE(,ROWS(Tbl_Allo)), y, SEQUENCE(Val_Repeat,,0), a, CHOOSEROWS(Tbl_Allo,TOCOL(IF({1},x,y))), HSTACK(DROP(a,,-1),TAKE(a,,-1)+TOCOL(IF(x,y))) ),5)
Dynamic array formulas.