Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPYING FORMULAS | Excel Worksheet Functions | |||
Copying Formulas | Excel Discussion (Misc queries) | |||
copying formulas in vba | Excel Discussion (Misc queries) | |||
Copying formulas | Excel Discussion (Misc queries) | |||
copying formulas | Excel Worksheet Functions |