How to generate 111112222233333 in cells

[riteon]

Hi Guys,

How can I populate cells vertically with numbered - e.g. 5 x 1's then 5 x 2's then 5 x 3's up to 100 (making 500 cells populated?) Obviously I may want to use other numbers and/or a higher/lower quantity but need the general idea in order to do this. E.g. the rule would be something like this; start number, times to print before increment, number of times to increment. Thanks

A
-
1
1
1
1
1
2
2
2
2
2
3
3
3
3
3


Thanks in advance

 

[Gerald Higgins]

For the specific number range you have shown, there are various methods, here's one.

Assuming you want the first 1 in cell A1...

=ROUNDUP(ROW()/5,0)

and copy down to row 100.

If you're starting in A2 instead of A1, use this instead...

=ROUNDUP((ROW()-1)/5,0)

 

[riteon]

For the specific number range you have shown, there are various methods, here's one.

Assuming you want the first 1 in cell A1...

=ROUNDUP(ROW()/5,0)

and copy down to row 100.

If you're starting in A2 instead of A1, use this instead...

=ROUNDUP((ROW()-1)/5,0)

Hi gerald, thanks for the speedy reply.

How about if I wanted to start the sequence with X000055 (produce this 12 times) then X000056 (then repeat this 12 times).

Thanks

 

[Gerald Higgins]

Starting in A1...

="X"&TEXT(54+ROUNDUP(ROW()/12,0),"000000")

 

[riteon]

Thank you very much Gerald All clear now.