Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Joining 2 if functions | Excel Discussion (Misc queries) | |||
Joining only certain columns to a spreadsheet. | Excel Discussion (Misc queries) | |||
Joining worksheets | Excel Worksheet Functions | |||
HELP! Joining two docs! | Excel Discussion (Misc queries) | |||
Macros for find and replace and then joining columns | Excel Discussion (Misc queries) |