Could I have some help with this project? Repeat a set of cells down the length of a column

[moonsugar]

Hi there!

I've been using Google Sheets for some years, and have just come back to Excel for the first time since 2007.
I've just spent perhaps the better part of an hour trying to search for what I'm trying to accomplish, with no luck.
Somehow I've managed to do it before a few times, but I guess I keep forgetting?
Anyway, here's what I'm working with:

Game.xlsx
	A	B
1	Very long pause
2	Run
3	Short pause
4	Jump
5	Wait
6	Enter
7	Long pause
8	Look
9	Short pause
10		Apple
11	Short pause
12	Jump
13	Short pause
14	Enter
15	Short pause
16	Run
17	Short pause
18	Enter
19	Long pause
20	Look
21	Short pause
22		Banana
23	Short pause
24	Jump
25	Short pause
26	Enter
27	Short pause
28	Run
29	Short pause
30	Enter
31	Long pause
32	Look
33	Short pause
34		Coconut
Fruit

Here, the blue cells go at the beginning. The green cells are the steps I'd like repeated, until I reach the bottom of the spreadsheet, which will be row 33237. The yellow cells are the fruit variables - we see here apple, banana and coconut, but there are 2769 of them.

It's important to leave a space after each set of green cells because after I've copied them down to the bottom of the sheet, I'm going to merge columns A and B together with "A1&B1", "A2&B2", "A3&B3" etc.

The end result should look like this:

Game.xlsx
	A
1	Very long pause
2	Run
3	Short pause
4	Jump
5	Wait
6	Enter
7	Long pause
8	Look
9	Short pause
10	Apple
11	Short pause
12	Jump
13	Short pause
14	Enter
15	Short pause
16	Run
17	Short pause
18	Enter
19	Long pause
20	Look
21	Short pause
22	Banana
23	Short pause
24	Jump
25	Short pause
26	Enter
27	Short pause
28	Run
29	Short pause
30	Enter
31	Long pause
32	Look
33	Short pause
34	Coconut
Fruit

But for all 2769 variables.

Currently, the rest of the sheet below "coconut" looks like this.

Game.xlsx
	A	B
22		Banana
23	Short pause
24	Jump
25	Short pause
26	Enter
27	Short pause
28	Run
29	Short pause
30	Enter
31	Long pause
32	Look
33	Short pause
34		Coconut
35
36
37
38
39
40
41
42
43
44
45
46		Dragonfruit
47
48
49
50
51
52
53
54
55
56
57
58		Elderberry
Fruit

What is the best way to go about populating the series of 11 steps plus one blank space in column A, as seen with "banana", for the other ~2700 fruit variables?

Because of the size of the data, it seems inefficient to click and drag with the fill handle (not that I've been able to get it to work recently anyway) - especially if I want to upscale it in the future. I've been using CTRL+Home and CTRL+End to navigate from the top to the bottom of the sheet. Columns C through XYZ, and rows 33238-100000 are hidden for ease of navigation.

I've come here with my specific problem so that rather than just apply a solution and forget how to do it ten minutes later, I can use this as a framework to solve other similar problems in the future. Many kind thanks to anyone with help or advice!

 

[alz]

Hi .a few suggestion to populate 11 step(could be text ,number ,...) Plus Blank (Total 12 step )

Excel Message board.xlsm
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12
13	1
Sheet1

Cell Formulas
Range	Formula
A13	A13	=A1

put = A1 on 13th row
then type A33238 on Cell address bar
Enter
Ctrl+Shift+UP
Ctrl+D
Copy
Paste as value
Ctrl+F (Fine 0 and Delete)

Hope this is useful!

 

[jolivanes]

Like this?

Sub Maybe()
With Range("A23:A" & Cells(Rows.Count, 2).End(xlUp).Row + 11)
    .Formula = "=R[-12]C"
    .Value = .Value
End With
End Sub

 

[moonsugar]

Like this?

Sub Maybe()
With Range("A23:A" & Cells(Rows.Count, 2).End(xlUp).Row + 11)
    .Formula = "=R[-12]C"
    .Value = .Value
End With
End Sub

I haven't heard of VBA codes before. Is there anything I should know?

Hi .a few suggestion to populate 11 step(could be text ,number ,...) Plus Blank (Total 12 step )

Excel Message board.xlsm
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12
13	1
Sheet1

Cell Formulas
Range	Formula
A13	A13	=A1

put = A1 on 13th row
then type A33238 on Cell address bar
Enter
Ctrl+Shift+UP
Ctrl+D
Copy
Paste as value
Ctrl+F (Fine 0 and Delete)

Hope this is useful!

This seems like it could do the trick! If I want to make some adjustments to the steps, will the rest of the document update as well?

 

[jolivanes]

How to Add Macro Code to Excel Workbook

How to copy Excel macro VBA code to your workbook, from website or sample file. Different types of code, where to paste it. Step-by-step videos, written steps

www.contextures.com

And there is always your favourite search site to find out how to insert macros into your project.

Don't quote if not necessary, which it seldom is. Refer to Post numbers instead.