Sequence Formula

[XxCALLofBOOTYxX]

Below I have written a sequence of #'s in Red. I would like to be able to type in my sequence (usually a 1-20 digit #) and then put how many iterations i would need in Red below that. I would then get my sequence generated in column "A". Thanks in advance to anyone who gives this a shot.

1	Sequence:	1	2	2	1	4	4
2	Iterations:	3
2
1
4
4
1
2
2
1
4
4
1
2
2
1
4
4

<tbody>
</tbody>

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H
1	1	Sequence:	1	2	2	1	4	4
2	2	Iterations:	3
3	2
4	1
5	4
6	4
7	1
8	2
9	2
10	1
11	4
12	4
13	1

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula A1 =IF(ROW()>$C$2*COUNTA($C$1:$Z$1),"",INDEX($C$1:$Z$1,MOD(ROW()-1,COUNTA($C$1:$Z$1))+1)) <tbody> </tbody>

<tbody>
</tbody>

We will need to tweak it a bit if you don't start in row 1.

 

[XxCALLofBOOTYxX]

This is perfect. Saved me a lot of headache. Thanks!

 

[CyrusTheVirus]

Looks like you are all set (Eric is the man), but just offering a Power Query option.

Create two tables... 1) With the number of iterations you want in it, then bring it into power query, then drill down first cell, then label table 'Iterations', then create connection only. 2) A table with your sequence (broken out in rows, like below).

Table 1)

No. Of Iterations

3

<tbody>
</tbody>

Table 2)

Sequence

1

2

2

1

4

4

<tbody>
</tbody>

Table 2 code (table name might need changing, depending), then bring it into a table and refresh as needed:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Sequence] <> null)),
    RepeatRows = Table.Repeat(#"Filtered Rows",Iterations)
in
    RepeatRows

Delivered Result:

Sequence
1
2
2
1
4
4
1
2
2
1
4
4
1
2
2
1
4
4

<tbody>
</tbody>

 

[Rick Rothstein]

And here is a macro solution that will work no matter how many numbers there are in your sequence...

[table="width: 500"]
[tr]
	[td]Sub RepeatedSequence()
  Dim Seq As String, Nums() As String
  Nums = Split(Application.Rept(Join(Application.Index(Range("C1", Cells(1, Columns.Count).End(xlToLeft)).Value, 1, 0)) & " ", Range("C2")))
  Range("A1").Resize(UBound(Nums)) = Application.Transpose(Nums)
End Sub[/td]
[/tr]
[/table]