Auto Replicate data to new rows based on number of chances

[AriesCastillo]

Hi Experts,

Need help on the following.

I have list of data with corresponding number of chances ( see attached column E)
I need to replicate the data under column A,B,C & D to a new rows as per the number under column E and so goes till the end of the list using a formula. No VB please as I have no idea how to use it.

Basically in the attached example i should have a 43 new data rows

Will really appreciate any kind of help...

[Pete_UK]

The CID number in your example seems to increment up to 36 and then to remain the same - can you explain the logic of this?

Pete

[Pete_UK]

The attached file shows how you can do this with 4 basic formulae. Put this in G2:

=IF(E2="","-",G1+E2)

then copy it down to beyond the bottom of your data (until you get hyphens). It is important to put zero in cell G1. This formula gives you a cumulative sum of the number of chances.

Then in cell I2 you can have this formula:

=IF(ROWS($1:1)>MAX(G:G),"-",MATCH(ROWS($1:1)-1,G:G)+1)

which determines the row for the next record to be fetched across to the new table. This formula in J2:

=IF(OR(I2="",I2="-"),"",COUNTIF(I$2:I2,I2))

gives you the sample number for each record, and this formula in K2:

=IF(OR($I2="",$I2="-"),"",INDEX(A:A,$I2))

brings the data from column A for the first record. This formula is then copied across to N2 to fetch the data from the other columns, and then the formulae in I2:N2 can be copied down as far as yo think you need them (i.e. until you get hyphens in column I).

Hope this helps.

Pete

[AriesCastillo]

Great this is exactly what i need.
Thanks!

[Pete_UK]

Glad to be able to help. Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete