Spill Sequence referencing three columns of data

[sabin348]

I found this thread where the solution where the solution from @smozgur woks great except I have three columns of data instead of two. I'm having trouble trying to take the formula and scale it for three columns.

Here's the thread link:

How can I repeat and spill like the picture example?

How can I create a formula that will repeat each item in the first column and spill with each date listed in the column next to it?

www.mrexcel.com

Here's his formula:

= LET(stepRows, ROWS(steps),    dateRows, ROWS(dates),    seq, SEQUENCE(stepRows * dateRows, 2),    IF(ISEVEN(seq),      INDEX(dates,MOD(SEQUENCE(stepRows * dateRows)-1,dateRows)+1),      INDEX(steps, INT(((seq+1)/2-1)/dateRows)+1)    )  )= LET(stepRows, ROWS(steps),    dateRows, ROWS(dates),    seq, SEQUENCE(stepRows * dateRows, 2),    IF(ISEVEN(seq),      INDEX(dates,MOD(SEQUENCE(stepRows * dateRows)-1,dateRows)+1),      INDEX(steps, INT(((seq+1)/2-1)/dateRows)+1)    )  )

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2		003	123	A		003	123	A
3		004	345	B		003	123	B
4		007	567			003	345	A
5		009	789			003	345	B
6						003	567	A
7						003	567	B
8						003	789	A
9						003	789	B
10						004	123	A
11						004	123	B
12						004	345	A
13						004	345	B
14						004	567	A
15						004	567	B
16						004	789	A
17						004	789	B
18						007	123	A
19						007	123	B
20						007	345	A
21						007	345	B
22						007	567	A
23						007	567	B
24						007	789	A
25						007	789	B
26						009	123	A
27						009	123	B
28						009	345	A
29						009	345	B
30						009	567	A
31						009	567	B
32						009	789	A
33						009	789	B
Sheet5

Cell Formulas
Range	Formula
F2:H33	F2	=LET(a,B2:B5,b,C2:C5,c,D2:D3,ra,ROWS(a),rb,ROWS(b),rc,ROWS(c),HSTACK(TOCOL(IF(SEQUENCE(,rb*rc),a,1/0),2),INDEX(b,MOD(INT(SEQUENCE(ra*rb*rc,,0,1/rc)),ra)+1),TOCOL(IF(SEQUENCE(,ra*rb),c,1/0),2,1)))
Dynamic array formulas.

 

[sabin348]

The data input for each column is very dynamic. Sometimes only a couple of rows, other times many rows. This formula is inputting zeros if there isn't anything in the cell.

 

[Fluff]

Did you set the ranges to where the data is? Or did you include blank cells?

 

[sabin348]

Did you set the ranges to where the data is? Or did you include blank cells?

Yes, as long all cells in the data range is populated, there's no problem. However, sometimes the there may be blanks at the bottom of the range to which the formula is interpreting it as zeros. Ideally, I need to either expand the the formula range to encompass a large number of rows (keeping in mind there may be blanks), or reference either a spill formula or a dynamic range.

 

[Fluff]

Are those 3 columns individual spill ranges?

 

[sabin348]

Are those 3 columns individual spill ranges?

Currently, the first two are, and the third is static with formulas.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2		003	123	A		003	123	A
3		004	345	B		003	123	B
4		007	567			003	345	A
5		009	789			003	345	B
6						003	567	A
7						003	567	B
8						003	789	A
9						003	789	B
10						004	123	A
11						004	123	B
12						004	345	A
13						004	345	B
14						004	567	A
15						004	567	B
16						004	789	A
17						004	789	B
18						007	123	A
19						007	123	B
20						007	345	A
21						007	345	B
22						007	567	A
23						007	567	B
24						007	789	A
25						007	789	B
26						009	123	A
27						009	123	B
28						009	345	A
29						009	345	B
30						009	567	A
31						009	567	B
32						009	789	A
33						009	789	B
Sheet5

Cell Formulas
Range	Formula
B2:C5	B2	=I2:I5
F2:H33	F2	=LET(a,B2#,b,C2#,c,FILTER(D2:D10,D2:D10<>""),ra,ROWS(a),rb,ROWS(b),rc,ROWS(c),HSTACK(TOCOL(IF(SEQUENCE(,rb*rc),a,1/0),2),INDEX(b,MOD(INT(SEQUENCE(ra*rb*rc,,0,1/rc)),ra)+1),TOCOL(IF(SEQUENCE(,ra*rb),c,1/0),2,1)))
Dynamic array formulas.

 

[sabin348]

Maybe this will help clarify the problem.

 

[Fluff]

Oops, had the wrong value in the Mod function, it should be

=LET(a,B2#,b,C2#,c,FILTER(D2:D10,D2:D10<>""),ra,ROWS(a),rb,ROWS(b),rc,ROWS(c),HSTACK(TOCOL(IF(SEQUENCE(,rb*rc),a,1/0),2),INDEX(b,MOD(INT(SEQUENCE(ra*rb*rc,,0,1/rc)),rb)+1),TOCOL(IF(SEQUENCE(,ra*rb),c,1/0),2,1)))