How can I repeat and spill like the picture example?

[VBE313]

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?

 

[VBE313]

Repeat of this question. Can I recreate this VBA Function with only using Excel Built-in Functions?

 

[smozgur]

@VBE313 - It looks like you asked this question by forgetting that it was already answered in one of your old questions. I can delete this thread. Otherwise, it will be really strange to mark your own post as the solution that leads to one of your own questions answered by someone else.

Or, another approach, I will provide an alternative LET solution, and then we can keep the thread live as it will provide an alternative solution to the future readers.

Book1
	A	B	C	D	E
1	Step 1	4/14/21		Step 1	4/14/21
2	Step Two	4/15/21		Step 1	4/15/21
3	Type Three	4/16/21		Step 1	4/16/21
4		4/17/21		Step 1	4/17/21
5		4/18/21		Step 1	4/18/21
6		4/19/21		Step 1	4/19/21
7		4/20/21		Step 1	4/20/21
8		4/21/21		Step 1	4/21/21
9		4/22/21		Step 1	4/22/21
10				Step Two	4/14/21
11				Step Two	4/15/21
12				Step Two	4/16/21
13				Step Two	4/17/21
14				Step Two	4/18/21
15				Step Two	4/19/21
16				Step Two	4/20/21
17				Step Two	4/21/21
18				Step Two	4/22/21
19				Type Three	4/14/21
20				Type Three	4/15/21
21				Type Three	4/16/21
22				Type Three	4/17/21
23				Type Three	4/18/21
24				Type Three	4/19/21
25				Type Three	4/20/21
26				Type Three	4/21/21
27				Type Three	4/22/21
Sheet1

Cell Formulas
Range	Formula
D1	D1	= 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) ) )

Named Ranges
Name	Refers To	Cells
dates	=Sheet1!$B$1:$B$9	D1
steps	=Sheet1!$A$1:$A$3	D1

In fact, we don't even have to name the ranges but select whole range and let the function deal with the separation:

Book1
	A	B	C	D	E
1	Step 1	4/14/21		Step 1	4/14/21
2	Step Two	4/15/21		Step 1	4/15/21
3	Type Three	4/16/21		Step 1	4/16/21
4		4/17/21		Step 1	4/17/21
5		4/18/21		Step 1	4/18/21
6		4/19/21		Step 1	4/19/21
7		4/20/21		Step 1	4/20/21
8		4/21/21		Step 1	4/21/21
9		4/22/21		Step 1	4/22/21
10				Step Two	4/14/21
11				Step Two	4/15/21
12				Step Two	4/16/21
13				Step Two	4/17/21
14				Step Two	4/18/21
15				Step Two	4/19/21
16				Step Two	4/20/21
17				Step Two	4/21/21
18				Step Two	4/22/21
19				Type Three	4/14/21
20				Type Three	4/15/21
21				Type Three	4/16/21
22				Type Three	4/17/21
23				Type Three	4/18/21
24				Type Three	4/19/21
25				Type Three	4/20/21
26				Type Three	4/21/21
27				Type Three	4/22/21
Sheet3

Cell Formulas
Range	Formula
D1	D1	= LET(data, $A$1:$B$9, stepRows, COUNTA(INDEX(data,,1)), dateRows, COUNTA(INDEX(data,,2)), seq, SEQUENCE(stepRows * dateRows, 2), IF(ISEVEN(seq), INDEX(data,MOD(SEQUENCE(stepRows * dateRows)-1,dateRows)+1,2), INDEX(data, INT(((seq+1)/2-1)/dateRows)+1,1) ) )

 

[sabin348]

@smozgur This formula is perfect and helps me with my project. I need to modify it for three columns of data, but I'm struggling to adjust it. Can you help, or do I need to start a new thread?

 

[smozgur]

@smozgur This formula is perfect and helps me with my project. I need to modify it for three columns of data, but I'm struggling to adjust it. Can you help, or do I need to start a new thread?

Yes, please create a new thread for your own question that refers to this thread with a link and/or use the formula from here that helped you in your project.