Hyterate copy and paste a x number of time

[davide88]

Hi everyone,

I have a list of labels (e.g. A,B,C,D,....) and I need to repeat each label a fixed amount of time (e.g. 5) by rows. In such a way I would get the result as in the attached example.

Example.xls

I think the fast solution is to use a simple macro but I do not know how to do it.

Thanks in advance.

Davide

[Jakobshavn]

Here is the macro:

Please Login or Register  to view this content.

[Tony Valko]

Just for the heck of it here's a formula method.

In B3:B6 enter the number of times you want each item to be repeated.

Enter this array formula** in C3:

=IF(ROWS(C$3:C3)>SUM(B$3:B$6),"",INDEX(A$3:A$6,MIN(IF(SUBTOTAL(9,OFFSET(B$3,,,ROW(INDIRECT("1:4"))))>=ROWS(C$3:C3),ROW(INDIRECT("1:4"))))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[davide88]

Thanks both for your replies.

About the macro, I am working on it.
About the formulas, it seems not working....I get only A repeted every time I copy down!

[ChemistB]

You'll need to use CNTRL SHFT ENTER for Tony's formula (from your results, you did not?)

Here's a simple, non-arrayed formula
=INDEX($A$3:$A$6,INT((ROW(A1)-1)/5)+1)

[davide88]

I used CNTRL SHFT ENTER both highlighting all the 20 cells covered by the outcome and only in C3 as he said.
Anyway, the last works perfectly and I think can also make the macro working.

Thank you all!

[Tony Valko]

About the formulas, it seems not working....I get only A repeted every time I copy down!

Here's the sample file:

Example(1).xlsx

You can put any numbers you want in B3:B6 (but the formula is only copied down so far).

EDIT: If every entry is to be repeated the same number of times here's another shorter less complex formula.

No need to enter the repeat values in column B. This value will be entered into the formula.

Entered in C3 and copied down:

=INDEX(A$3:A$6,CEILING(ROWS(C$3:C3)/5,1))