Macro or Formula

[AzharSham]

All I have a 2 sets of data in 2 different sheets. Data A is 639 rows and Data B is 24 rows. I want to take the each value in Data A and Multiply it by the 24 different values in Data B. Essentially ending up with 15336 (639*24) rows of new data. Should I conduct this via a macro or formula using excel. Any assistance is appreciated.

 

[Legacy 143009]

Sheet3 A1= INDIRECT("Sheet1!A"&MOD(ROW(A1)-1,639)+1)*INDIRECT("Sheet2!A"&CEILING(ROW(A1)/24,1))

Hope it works

 

[AzharSham]

Just tried the formula. When I get to row 577 using the formula it seems to break and result in zeros.

 

[Legacy 143009]

Sorry, the right method should be:

Sheet3 A1= INDIRECT("Sheet1!A"&MOD(ROW(A1)-1,639)+1)*INDIRECT("Sheet2!A"&CEILING(ROW(A1)/639,1))

 

[AzharSham]

Data A

276

260

784

876

710

587 Data B 2.4% 1.5% 0.9% 0.5% 0.4% <colgroup><col style="width:48pt" width="64"> </colgroup><tbody> </tbody> Expected Results 6.554144 4.278875 2.539997 <colgroup><col style="width:48pt" width="64"> </colgroup><tbody> </tbody> Formula results 6.554144 6.169013 18.60051 <colgroup><col style="width:48pt" width="64"> </colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[AzharSham]

Doing some figuring out. The formula is actually reversed. I need to take Value 1 of Data A then Multiply by all of the data in Data B then move to next value in Data A.

 

[Legacy 143009]

Then may be;

Sheet3 A1= INDIRECT("Sheet1!A"&CEILING(ROW(A1)/639,1))*INDIRECT("Sheet2!A"&MOD(ROW(A1)-1,24)+1)

 

[Legacy 143009]

No the formula above wont work. Try;

Sheet3 A1= INDIRECT("Sheet1!A"&CEILING(ROW(A1)/24,1))*INDIRECT("Sheet2!A"&MOD(ROW(A1)-1,24)+1)

 

[AzharSham]

That worked. Thank you so much.