Formula needed to repeat pattern

[ymurray]

I have to populate various spreadsheets using the following number sequence:


1,1,1,1,1,1
2,2,2,2,2,2,2,2,2,2,2,2,2
3,3,3,3,3,3
4,4,4,4,4,4,4,4,4,4,4,4,4
etc..




So basically, I have to follow sequential numbers in the special pattern of 6 instances of 1, 13 instances of 2, 6 instances of 3, 13 instances of 4, etc. I have to go all the way down to 80 instances. Is there a formula I can use in order to not have to do this manually? I have about 4 different spreadsheets I need to do this for.

 

[tyija1995]

As it alternates 6 & 13 for numbers 1 to 80 in B1 put 6, B2 put 13 then copy these 2 cells all the way down to B80 (so that B1:B80) consists of 6,13,6,13...
Then in column C enter =1 In C1 and for C2 put =1+C1 -> drag this down to C80
Now in column A put in: (For cell A1)
=LEFT(REPT(C1&",",B1),LEN(REPT(C1&",",B1))-1)
Drag that down to A80

 

[Fluff]

Hi & welcome to MrExcel.
How about in A1
=REPT(ROW()&",",5)&ROW()
In A2
=REPT(ROW()&",",12)&ROW()
Then grab both cells & fill down

 

[ymurray]

I'm sorry for not explaining this better, but the numbers will actually be going down a column, not a row. So it will look like this:


1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
3
4
4
4
4
4
4
4
4
4
4
4
4
4

 

[tyija1995]

Okay so this should work for what you are looking for:

Firstly this macro assumes you have setup the data horizontally, to do this follow these steps:

1) Open a blank worksheet
2) Highlight A1:F1 and enter =ROW() followed by CTRL+Enter (1 should appear in cells A1:F1)
3) Highlight A2:M2 and enter = ROW() followed by CTRL+Enter (2 should appear in cells A2:M2)
4) Copy cells A1:M2 and highlight cells A3:A80 and paste
5) Copy A1:M80 and paste as values
6) With A1:M80 copied right click in cell N1 and Paste as transpose
7) Delete columns A:M so that you now have 80 columns of data (A:CB) which show 1 up to 80 as the columns progress
8) Run the macro below:

Sub CopyNums()
    Application.ScreenUpdating = False
    Dim j As Integer
        For j = 2 To 80
            Range(Cells(1, j), Cells(13, j)).Select
            Selection.Cut
            Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Select
            ActiveSheet.Paste
        Next j
    Application.ScreenUpdating = True
End Sub

Column A should now have 760 rows as desired (6+13)*40 (alternating patterns 6 + 13 multiplied 40 times as this is how many repetitions of the pattern there is)

 

[Fluff]

Another option

Sub ymurray()
   Dim i As Long, j As Long
   
   j = 1
   For i = 1 To 80 Step 2
      Cells(j, 1).Resize(6).Value = i
      Cells(j + 6, 1).Resize(13).Value = i + 1
      j = j + 19
   Next i
End Sub

 

[ymurray]

Okay so this should work for what you are looking for:

Firstly this macro assumes you have setup the data horizontally, to do this follow these steps:

1) Open a blank worksheet
2) Highlight A1:F1 and enter =ROW() followed by CTRL+Enter (1 should appear in cells A1:F1)
3) Highlight A2:M2 and enter = ROW() followed by CTRL+Enter (2 should appear in cells A2:M2)
4) Copy cells A1:M2 and highlight cells A3:A80 and paste
5) Copy A1:M80 and paste as values
6) With A1:M80 copied right click in cell N1 and Paste as transpose
7) Delete columns A:M so that you now have 80 columns of data (A:CB) which show 1 up to 80 as the columns progress
8) Run the macro below:

Sub CopyNums()
    Application.ScreenUpdating = False
    Dim j As Integer
        For j = 2 To 80
            Range(Cells(1, j), Cells(13, j)).Select
            Selection.Cut
            Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Select
            ActiveSheet.Paste
        Next j
    Application.ScreenUpdating = True
End Sub

Column A should now have 760 rows as desired (6+13)*40 (alternating patterns 6 + 13 multiplied 40 times as this is how many repetitions of the pattern there is)

I'm with you up until running the macro. Whenever I run the macro, I receive an error that says:

Compile Error

Only comments may appear after End Sub, End Function, or End Property.

 

[shg]

Or a formula:

	A​	B​
1​	​	​
2​	1​	A2: =4*INT((ROWS($A$2:A2) - 1)/38) + MATCH(MOD(ROWS(A$2:A2) - 1, 38), {0,6,19,25})
3​	1​
4​	1​
5​	1​
6​	1​
7​	1​
8​	2​
9​	2​
10​	2​
11​	2​
12​	2​
13​	2​
14​	2​
15​	2​
16​	2​
17​	2​
18​	2​
19​	2​
20​	2​
21​	3​
22​	3​
23​	3​
24​	3​
25​	3​
26​	3​
27​	4​
28​	4​
29​	4​
30​	4​
31​	4​
32​	4​
33​	4​
34​	4​
35​	4​
36​	4​
37​	4​
38​	4​
39​	4​
40​	5​
41​	5​

 

[tyija1995]

I'm with you up until running the macro. Whenever I run the macro, I receive an error that says:

Compile Error

Only comments may appear after End Sub, End Function, or End Property.

Strange, it works just fine for me, have you tried pasting the code in to a new module within the workbook?

From github the error states: "

• You placed executable code outside a procedure. Any nondeclarative lines outside a procedure must begin with a comment delimiter ( ' ). Declarative statements must appear before the first procedure declaration. Comments are ignored when the code executes."

 

[ymurray]

Thank you to all of you for your help! You saved me a lot of time!