Joining Columns

[Michael168]

I need help to combine the two ranges together to give me the new row
in a new sheet.

Sheet1 has the two ranges i.e. A1:C50 , E1:G50.

How to use a VBA module to create the new rows under the belo
conditions.

A1:C1+E1:G1 continue until A1:C1+E50:G50
This combination will produce 50 rows (A1:F50)

then
A2:C2+E1:G1 continue until A2:C2+E50:G50
This combination will produce 50 rows (A51:F100)

until the last conditions

A50:C50+E1:G1 continue until A1:C1+E50:G50

So after completing combining A1:C50 with E1:G50, Sheet2 will have 250
rows (50x50) (A1:F2500)

Thank you.

Regards
Michae

 

[Tom Ogilvy]

Put this formula in A1 on the new sheet, then drag fill down to row 2500

=SUM(OFFSET(Sheet1!$A$1,TRUNC((ROW()-1)/50),0,1,3),OFFSET(Sheet1!$E$1,MOD(RO
W()-1,50),0,1,3))

 

[Michael168]

Hi ! Tom Ogilvy,

Thanks for your help, but the formula give me the sum value. What
want is the individual values in six columns A:F in sheet2 from joinin
the A:C & E:F from sheet1.

Regards
Michael

 

[Tom Ogilvy]

A1: =OFFSET(Sheet1!$A$1,TRUNC((ROW()-1)/50),0,1,1)
B1: =OFFSET(Sheet1!$A$1,TRUNC((ROW()-1)/50),1,1,1)
C1: =OFFSET(Sheet1!$A$1,TRUNC((ROW()-1)/50),2,1,1)
D1: =OFFSET(Sheet1!$E$1,MOD(ROW()-1,50),0,1,1))
E1: =OFFSET(Sheet1!$E$1,MOD(ROW()-1,50),1,1,1))
F1: =OFFSET(Sheet1!$E$1,MOD(ROW()-1,50),2,1,1))

Select A1:F1, then drag fill down to 2500

 

[Soo Cheon Jheong]

Michael,

Put this formula in A1 on the new sheet,
then drag fill down and fill right to C2500.

=OFFSET(Sheet1!$A$1,(ROW()-1)/50,COLUMN()-1)

Put this formula in D1 on the new sheet,
then drag fill down and fill right to F2500.

=OFFSET(Sheet1!$E$1,MOD((ROW()-1),50),COLUMN(A1)-1)

 

[Michael168]

Hi!,Tom Ogilvy

Your second time formula works perfectly except when I try to us
header rows in sheet2 the formula cannot shift accordingly.

Can you guide me again,says I want the formula to start from A5 i
sheet2 without any changes in sheet1?

Is it hard to code the routine in VBA?

Thanks
Michael

 

[Tom Ogilvy]

In A5 put in the formula

=OFFSET(Sheet1!$A$1,TRUNC((ROW(A1)-1)/50)+1,COLUMN()-1,1,1)

drag fill to column C

In D5 put in the formula
=OFFSET(Sheet1!$E$1,MOD(ROW(A1)-1,50)+1,COLUMN()-4,1,1)

drag fill to column G

then select A5:G5 and drag fill down 2500 rows.

for a macro

Sub FillWithData()
Dim rng as Range
Range("A5").Resize(2500,3).Formula = _
"=OFFSET(Sheet1!$A$1,TRUNC((ROW(A1)-1)/50)+1,COLUMN()-1,1,1)"
Range("D5").Resize(2500,3).Formula = _
"=OFFSET(Sheet1!$E$1,MOD(ROW(A1)-1,50)+1,COLUMN()-4,1,1)"
set rng = Range("A5").Resize(2500,6)
rng.Formula = rng.Value

End Sub

 

[Michael168]

Hi! Tom Ogilvy,

Thank you for helping me and helping me to understand more.

Reagrds
Michae