Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Rows Multiple times "changing" values in one column
Hi
Thanks for your help in advance. I will try and explain the probelm with an example. Sheet 1: "Raw Data" Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Some 1 51 41 75 50 A6 HGH NUM Some 2 58 70 62 72 B6 HGH NUM Some 3 61 24 74 43 A4 HGH NUM Sheet 2" "Structure" Structure6 Structure5 Structure4 Structure3 Structure2 Structure1 A6 A5 A4 A3 A2 A1 B6 B5 B4 B3 B2 B1 Column called Data 6 in Sheet 1 corresponds to the data in Seeet 2. Lets call it the LEVEL in the structure. My task is to copy all the data in each row of Sheet 1 for all LEVELs "Below" the relevant structure. For instance, in the first row, the Level is "A6", then in a new sheet I would like the entire row copied (many times) and the only cell that needs to be changed in each copy is A6. So the first row in this example will be repeated 6 times (once as A6, and in the other "copied rows", A6 will be replaced by A5, A4, A3, A2, A1 in the other 5 rows.) Just to exapnd on the point, in case of ROW 3, where the level is A4, the other 3 rows will have A3, A2, A1. (((I used numbers (A6) here but the real data is all TEXT. SO the macro needs to look for the value in "data 6" column in the Structure sheet and create copies of that entire row with all values in the structure to the "right" of the "matching" value in structure sheet. I guess I repeated myself twice or thrice here. Again thanks for any help / guidance you can provide. Best Regards Prasad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Rows Multiple times "changing" values in one column
Try this code
Sub move_data() Const FirstRow = 2 Const FirstCol = "A" Level = InputBox("enter Column Number of Level : ") StructRowCount = 1 With Sheets("Raw Data") LastRow = .Cells(Rows.Count, FirstCol).End(xlUp).Row For RawRowCount = FirstRow To LastRow For LevelCount = 0 To (Level - 1) Sheets("Structure"). _ Range("A" & StructRowCount). _ Offset(0, LevelCount).Value = _ .Range(FirstCol & RawRowCount). _ Offset(0, Level - LevelCount - 1). _ Value Next LevelCount StructRowCount = StructRowCount + 1 Next RawRowCount End With End Sub "EE" wrote: Hi Thanks for your help in advance. I will try and explain the probelm with an example. Sheet 1: "Raw Data" Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Some 1 51 41 75 50 A6 HGH NUM Some 2 58 70 62 72 B6 HGH NUM Some 3 61 24 74 43 A4 HGH NUM Sheet 2" "Structure" Structure6 Structure5 Structure4 Structure3 Structure2 Structure1 A6 A5 A4 A3 A2 A1 B6 B5 B4 B3 B2 B1 Column called Data 6 in Sheet 1 corresponds to the data in Seeet 2. Lets call it the LEVEL in the structure. My task is to copy all the data in each row of Sheet 1 for all LEVELs "Below" the relevant structure. For instance, in the first row, the Level is "A6", then in a new sheet I would like the entire row copied (many times) and the only cell that needs to be changed in each copy is A6. So the first row in this example will be repeated 6 times (once as A6, and in the other "copied rows", A6 will be replaced by A5, A4, A3, A2, A1 in the other 5 rows.) Just to exapnd on the point, in case of ROW 3, where the level is A4, the other 3 rows will have A3, A2, A1. (((I used numbers (A6) here but the real data is all TEXT. SO the macro needs to look for the value in "data 6" column in the Structure sheet and create copies of that entire row with all values in the structure to the "right" of the "matching" value in structure sheet. I guess I repeated myself twice or thrice here. Again thanks for any help / guidance you can provide. Best Regards Prasad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Rows Multiple times "changing" values in one column
Are you looking for a macro that will process every row in the sheet named
Raw Data (is the sheet named raw data or sheet1? Likewise is sheet2 named sheet2 or Structure) Where will the output go? -- Regards, Tom Ogilvy "EE" wrote: Hi Thanks for your help in advance. I will try and explain the probelm with an example. Sheet 1: "Raw Data" Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Some 1 51 41 75 50 A6 HGH NUM Some 2 58 70 62 72 B6 HGH NUM Some 3 61 24 74 43 A4 HGH NUM Sheet 2" "Structure" Structure6 Structure5 Structure4 Structure3 Structure2 Structure1 A6 A5 A4 A3 A2 A1 B6 B5 B4 B3 B2 B1 Column called Data 6 in Sheet 1 corresponds to the data in Seeet 2. Lets call it the LEVEL in the structure. My task is to copy all the data in each row of Sheet 1 for all LEVELs "Below" the relevant structure. For instance, in the first row, the Level is "A6", then in a new sheet I would like the entire row copied (many times) and the only cell that needs to be changed in each copy is A6. So the first row in this example will be repeated 6 times (once as A6, and in the other "copied rows", A6 will be replaced by A5, A4, A3, A2, A1 in the other 5 rows.) Just to exapnd on the point, in case of ROW 3, where the level is A4, the other 3 rows will have A3, A2, A1. (((I used numbers (A6) here but the real data is all TEXT. SO the macro needs to look for the value in "data 6" column in the Structure sheet and create copies of that entire row with all values in the structure to the "right" of the "matching" value in structure sheet. I guess I repeated myself twice or thrice here. Again thanks for any help / guidance you can provide. Best Regards Prasad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Rows Multiple times "changing" values in one column
On Oct 11, 4:11 am, Tom Ogilvy
wrote: Are you looking for a macro that will process every row in the sheet named Raw Data (is the sheet named raw data or sheet1? Likewise is sheet2 named sheet2 or Structure) Where will the output go? -- Regards, Tom Ogilvy "EE" wrote: Hi Thanks for your help in advance. I will try and explain the probelm with an example. Sheet 1: "Raw Data" Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Some 1 51 41 75 50 A6 HGH NUM Some 2 58 70 62 72 B6 HGH NUM Some 3 61 24 74 43 A4 HGH NUM Sheet 2" "Structure" Structure6 Structure5 Structure4 Structure3 Structure2 Structure1 A6 A5 A4 A3 A2 A1 B6 B5 B4 B3 B2 B1 Column called Data 6 in Sheet 1 corresponds to the data in Seeet 2. Lets call it the LEVEL in the structure. My task is to copy all the data in each row of Sheet 1 for all LEVELs "Below" the relevant structure. For instance, in the first row, the Level is "A6", then in a new sheet I would like the entire row copied (many times) and the only cell that needs to be changed in each copy is A6. So the first row in this example will be repeated 6 times (once as A6, and in the other "copied rows", A6 will be replaced by A5, A4, A3, A2, A1 in the other 5 rows.) Just to exapnd on the point, in case of ROW 3, where the level is A4, the other 3 rows will have A3, A2, A1. (((I used numbers (A6) here but the real data is all TEXT. SO the macro needs to look for the value in "data 6" column in the Structure sheet and create copies of that entire row with all values in the structure to the "right" of the "matching" value in structure sheet. I guess I repeated myself twice or thrice here. Again thanks for any help / guidance you can provide. Best Regards Prasad- Hide quoted text - - Show quoted text - Thanks Tom. Yes on both counts. It will have to run on all rows in Raw Data Sheet. Sheet 2 name is "Structure" The expectation is that the final output is in a new sheet with the original data remaining intact. Thanks again. Best Regards Prasad |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Rows Multiple times "changing" values in one column
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find "Cancellation" in column "A" and copy all data from Columns B-F onto another Sheet | Excel Programming | |||
delete rows with null values in "M" column | Excel Programming | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions | |||
Changing format of column from "general" to "currency" | Excel Programming | |||
Can you "duplicate" "copy" listboxes and code to multiple cells? | Excel Programming |