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
[TABLE="width: 88"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]276
[/TD]
[/TR]
[TR]
[TD="align: right"]260[/TD]
[/TR]
[TR]
[TD="align: right"]784[/TD]
[/TR]
[TR]
[TD="align: right"]876[/TD]
[/TR]
[TR]
[TD="align: right"]710[/TD]
[/TR]
[TR]
[TD="align: right"]587
Data B
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2.4%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1.5%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.9%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.5%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.4%[/TD]
[/TR]
</tbody>[/TABLE]
Expected Results
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]6.554144[/TD]
[/TR]
[TR]
[TD="align: right"]4.278875[/TD]
[/TR]
[TR]
[TD="align: right"]2.539997[/TD]
[/TR]
</tbody>[/TABLE]

Formula results
[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]6.554144[/TD]
[/TR]
[TR]
[TD="align: right"]6.169013[/TD]
[/TR]
[TR]
[TD="align: right"]18.60051[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.