Formulas for Copying Down
 

Excel2003

Cols H, I, J contain:

Col H ... Values 1, 2, 3 ... 100 (Ascend)
Col I ... Random Values (Ascend)
Col J ... Values L1, L2, L3 ... L50 (Ascend)

I am looking for easiest way to copy down the following 3 Cols.

Col A ... Repeat each value from Col H (50 times) ... thru value 100
Col C ... Repeat each Value from Col I (50 times) ...
Col D ... Values L1, L2, L3 thru L50 ... (100 Times)

Will look something like:

1 ... Value from Cell I2 ... L1
1 ... Value from Cell I2 ... L2
1 ... Value from Cell I2 ... L3
50 Times ... followed by
2 ... Value from Cell I3 ... L1
2 ... Value from Cell I3 ... L2
2 ... Value from Cell I3 ... L3
50 Times etc out to value 100

Total File size approx 5000 Records

Thanks ... Kha

Formulas for Copying Down
 

Col A formula:
=INT((ROW()+49)/50)

Col B formula:
=INDEX(I:I,A1)

Col C formula:
="L"&MOD(ROW()-1,50)+1
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ken" wrote:

Excel2003

Cols H, I, J contain:

Col H ... Values 1, 2, 3 ... 100 (Ascend)
Col I ... Random Values (Ascend)
Col J ... Values L1, L2, L3 ... L50 (Ascend)

I am looking for easiest way to copy down the following 3 Cols.

Col A ... Repeat each value from Col H (50 times) ... thru value 100
Col C ... Repeat each Value from Col I (50 times) ...
Col D ... Values L1, L2, L3 thru L50 ... (100 Times)

Will look something like:

1 ... Value from Cell I2 ... L1
1 ... Value from Cell I2 ... L2
1 ... Value from Cell I2 ... L3
50 Times ... followed by
2 ... Value from Cell I3 ... L1
2 ... Value from Cell I3 ... L2
2 ... Value from Cell I3 ... L3
50 Times etc out to value 100

Total File size approx 5000 Records

Thanks ... Kha

Formulas for Copying Down
 

Hi Ken

From the samples you posted I understand your data starts from Row2. If your
data starts from Row1 adjust the formulas to suit.

Apply this formula in A2 and copy/drag the formula to B2..Now copy the
formula down as required for ColA and ColB

=OFFSET(H$2,INT((ROW($A1)-1)/50),)

Apply the below in cell C2
=OFFSET($J$2,MOD(ROW(A1)-1,50),)

--
Jacob


"Ken" wrote:

Excel2003

Cols H, I, J contain:

Col H ... Values 1, 2, 3 ... 100 (Ascend)
Col I ... Random Values (Ascend)
Col J ... Values L1, L2, L3 ... L50 (Ascend)

I am looking for easiest way to copy down the following 3 Cols.

Col A ... Repeat each value from Col H (50 times) ... thru value 100
Col C ... Repeat each Value from Col I (50 times) ...
Col D ... Values L1, L2, L3 thru L50 ... (100 Times)

Will look something like:

1 ... Value from Cell I2 ... L1
1 ... Value from Cell I2 ... L2
1 ... Value from Cell I2 ... L3
50 Times ... followed by
2 ... Value from Cell I3 ... L1
2 ... Value from Cell I3 ... L2
2 ... Value from Cell I3 ... L3
50 Times etc out to value 100

Total File size approx 5000 Records

Thanks ... Kha

Formulas for Copying Down
 

Perfect ... Thanks ... Kha

"Jacob Skaria" wrote:

Hi Ken

From the samples you posted I understand your data starts from Row2. If your
data starts from Row1 adjust the formulas to suit.

Apply this formula in A2 and copy/drag the formula to B2..Now copy the
formula down as required for ColA and ColB

=OFFSET(H$2,INT((ROW($A1)-1)/50),)

Apply the below in cell C2
=OFFSET($J$2,MOD(ROW(A1)-1,50),)

--
Jacob


"Ken" wrote:

Excel2003

Cols H, I, J contain:

Col H ... Values 1, 2, 3 ... 100 (Ascend)
Col I ... Random Values (Ascend)
Col J ... Values L1, L2, L3 ... L50 (Ascend)

I am looking for easiest way to copy down the following 3 Cols.

Col A ... Repeat each value from Col H (50 times) ... thru value 100
Col C ... Repeat each Value from Col I (50 times) ...
Col D ... Values L1, L2, L3 thru L50 ... (100 Times)

Will look something like:

1 ... Value from Cell I2 ... L1
1 ... Value from Cell I2 ... L2
1 ... Value from Cell I2 ... L3
50 Times ... followed by
2 ... Value from Cell I3 ... L1
2 ... Value from Cell I3 ... L2
2 ... Value from Cell I3 ... L3
50 Times etc out to value 100

Total File size approx 5000 Records

Thanks ... Kha