How to repeat a range of cells in a column?

[Rnkhch]

Hello,

In the table below, I have a series of increasing numbers (e.g. 1-3) in column A, and my goal is to repeat this range vertically in column B up to say B500. However, if I add an extra number (in this example #4) to column A, I want column B to readjust the repeating range of cells (which I'm showing below in columns C and D). And the same if more numbers are added (or removed) in column A.

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

Is there a way to do this without VBA?

Thanks for any input.

 

[JEC]

In B1:

=MOD(ROW(A1)-1,MAX($A$1:$A$100))+1

In D1

=MOD(ROW(A1)-1,MAX($C$1:$C$100))+1

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1
2	1	10	1
3	2		2
4	3		3
5			1
6			2
7			3
8			1
9			2
10			3
11			1
12			2
13			3
14			1
15			2
16			3
17			1
18			2
19			3
20			1
21			2
22			3
23			1
24			2
25			3
26			1
27			2
28			3
29			1
30			2
31			3
Data

Cell Formulas
Range	Formula
C2:C31	C2	=LET(f,FILTER(A2:A100,A2:A100<>""),r,ROWS(f),INDEX(f,MOD(SEQUENCE(r*B2,,0),r)+1))
Dynamic array formulas.

Change B2 to reflect how many repeates you want.

 

[Sufiyan97]

Try

Book5
	A	B	C	D	E	F
1	Data	Requirement	Formula	Data	Requirement	Formula
2	1	1	1	1	1	1
3	2	2	2	2	2	2
4	3	3	3	3	3	3
5		1	1	4	4	4
6		2	2		1	1
7		3	3		2	2
8		1	1		3	3
9		2	2		4	4
10		3	3		1	1
11		1	1		2	2
12
13
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=MOD(ROWS(C$2:C2)-1,3)+1
F2:F11	F2	=MOD(ROWS(F$2:F2)-1,4)+1

 

[queuesmef]

If your data starts in Cell A1 to A3, then I would highlight the 3 Cells, Select COPY, GO to where you want to paste it (MOST IMPORTANT-must multiples of 3, so 90, or 93 etc) and highlight that Range (you will see the number of cells as you select them, again make sure it's a multiple of 3) then release and your 3 cells will be copied down

 

[Rnkhch]

Thanks all!! Excellent answers! I think I'll mark the answer by Fluff as the solution as you only need to put this formula in one cell, and an entire column is automatically generated, and I can also control how deep it goes by adjusting the number in cell B2. Very cool!

 

[Fluff]

Glad we could help & thanks for the feedback.