Formula needed to repeat pattern

ymurray · Apr 1, 2019

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 · Apr 1, 2019

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 · Apr 1, 2019

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 · Apr 1, 2019

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 · Apr 1, 2019

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:

Code:

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 · Apr 1, 2019

Another option

Code:

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 · Apr 1, 2019

tyija1995 said:
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:

Code:

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 · Apr 1, 2019

Or a formula:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A

[/td][td="bgcolor:#C0C0C0"]

B

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1

[/td][td="bgcolor:#F3F3F3"]

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2

[/td][td]

1

[/td][td]A2: =4*INT((ROWS($A$2:A2) - 1)/38) + MATCH(MOD(ROWS(A$2:A2) - 1, 38), {0,6,19,25})[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3

[/td][td]

1