Copy Rows Multiple times "changing" values in one column

[EE]

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

 

[Guest]

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

 

[Guest]

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?

 

[EE]

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
















- 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

 

[Guest]

Since it has been a while since this was posted and I doubt I will get back
here again, if you still want a macro, send me a sample workbook with the
description of your problem to (e-mail address removed)