Joining Columns
 

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

--
Message posted from http://www.ExcelForum.com

Joining Columns
 

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))

--
Regards,
Tom Ogilvy

"Michael168 " wrote in message
...
I need help to combine the two ranges together to give me the new rows
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 below
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 2500
rows (50x50) (A1:F2500)

Thank you.

Regards
Michael


---
Message posted from http://www.ExcelForum.com/

Joining Columns
 

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

--
Message posted from http://www.ExcelForum.com

Joining Columns
 

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

--
Regards,
Tom Ogilvy

"Michael168 " wrote in message
...
Hi ! Tom Ogilvy,

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

Regards
Michael.


---
Message posted from http://www.ExcelForum.com/

Joining Columns
 

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)

--
Regards,
Soo Cheon Jheong
Seoul, Korea

Joining Columns
 

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

--
Message posted from http://www.ExcelForum.com

Joining Columns
 

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

--
Regards,
Tom Ogilvy



"Michael168 " wrote in message
...
Hi!,Tom Ogilvy

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

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

Is it hard to code the routine in VBA?

Thanks
Michael.


---
Message posted from http://www.ExcelForum.com/

Joining Columns
 

Hi! Tom Ogilvy,

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

Reagrds
Michae

--
Message posted from http://www.ExcelForum.com