Mulitplying rows of data; copy one row and create five identical rows

**simonv74** · 05-08-2009, 02:23 PM

Hi there,

I have a spreadsheet with approx. 1900 rows, of which I need to duplicate each row and create five new rows all containing the same information. First three rows are:

Product code Name Price RRP
N0507 Foot pump 8.45 10.49
N0508 Tent peg 28.95 34.99

Which need to become:

Product code Name Price RRP
N0507 Foot Pump 8.45 10.49
N0507 Foot Pump 8.45 10.49
N0507 Foot Pump 8.45 10.49
N0507 Foot Pump 8.45 10.49
N0507 Foot Pump 8.45 10.49
N0508 Tent peg 28.95 34.99
N0508 Tent peg 28.95 34.99
N0508 Tent peg 28.95 34.99
N0508 Tent peg 28.95 34.99
N0508 Tent peg 28.95 34.99

And so on, and so on.

Can anyone help?

**zbor** · 05-08-2009, 02:59 PM

If your products are in A1 to A1900 try this in C for example:

=INDEX(A1:A1900;ROUNDUP(ROW(A1)/5;0))

And extend down as much as you need (Of course, you can put to count how many you need).

Also, replace ; with , if you need.

**simonv74** · 05-08-2009, 05:50 PM

Hi,

Thanks for your help, but the formula you suggested didn't work; I was presented with the error pane which said the formula contained an error - the last row number I have was also highlighted:

=INDEX(A2:A1912;ROUNDUP(ROW(A2)/5;0))

What do you think?

**zbor** · 05-09-2009, 03:44 AM

Last row number doesn't matter. You can put as much as you need...

You should add $

=INDEX($A$2:$A$1912,ROUNDUP(ROW(A1)/5,0))

Also, leave ROW(A1), doesn't matter where your data start.

Edit:

If you want to automaticly take care of empty places after last value

Please Login or Register  to view this content.

and if you want change number of multiply you can use some cell eg. B1 and replace number 5 with cell $B$1 so you can change it when you like 4,5,6,500 but you must put enough range $A$5000