Help copy row to new sheet x times depending on cell value.

[RandomPezzer]

Hi Guys,

Can someone help me please.

I have a spreadsheet which will have approx 1000 rows of data, the cells in column V will be updated to have a number from 0 - 10.

So if the cell V6 = 2 copy row V6 twice to sheet2, then if cell V10 = 3 copy row V10 three times to the next available row in sheet2,

If cells in column V are blank or = 0 do nothing.


Hope this makes sense.


Thanks

[Pete_UK]

Are you still using XL2003, as your profile states, or have you upgraded to a later version? This affects some of the functions that we can use.

Also, how many columns are you using? Is column V the final column?

Pete

[RandomPezzer]

Hi Pete, Using excel 2010 and column W is the final column.

[Pete_UK]

Okay, put this formula in cell X2 of the first sheet:

=IF(OR(V2=0,V2=""),"-",MAX(X$1:X1)+1)

and copy down to the bottom of your data. It allocates a sequential number to those records where the entry in column V is neither blank nor equal to zero.

Then in the second sheet you can use these formulae in the cells stated:

X2: =IF(ROWS($1:1)>MAX(Sheet1!X:X),"",MATCH(ROWS($1:1),Sheet1!X:X,0))

Y2: =IF(X2="","",INDEX(Sheet1!V:V,X2))

Z2: =IF(Y2="","",Y2+Z1)

and copy these down until you start to get blanks. You also need to put zero in cell Z1.

Then in A2 you can have this formula:

=IF(ROWS($1:1)>MAX($Z:$Z),"",INDEX(Sheet3!A:A,INDEX($X:$X,MATCH(ROWS($1:1)-1,$Z:$Z)+1)))

Copy this across to W2, and then copy the formulae in A2:W2 down as far as you need to (i.e. until you start to get blanks).

Hope this helps.

Pete

EDIT: please update your profile on UserCP to show the version of Excel that you are using.

[RandomPezzer]

Thank you so much Pete, that is really helpful and works a treat !!



PS Profile update

[Pete_UK]

You're welcome - thanks for feeding back.

Pete