Solved: Arrange/Lag Data in columns By Specfied Lags

[Hamond]

Hello All,

I'm currently doing this manually and sometimes it very easier to make a mistake so I'm looking for a more robust automated procedure.

I have data variables stored in columns in a sheet that I would like to re-arrange in terms of their starting/ending position via shifting each series down by a number of cells relative to a base row.

My current set up is as follows. I have two sheets. In the sheet called raw - I have the data variables stored in each column with row 5 as the base. I want to ignore the first two columns in the analysis (and always include them as they are in the final dataset)

In another sheet called Lags, I have the equivalent variables, (series names in the first row). Here I have two populated parameters for each variable:

a) Row 6: This either contains a 0 or 1. I only want to include those variables/columns that have a 1 in this row in the final dataset.

b) Row 4 The number of positions to shift each variable is specified here.

So for example, if row 6 contains a 1 and row 4 says 20, then I want to include this variable in the final dataset and shift the start of this series down by 20 cells from the base row 5 in the Raw sheet.

If row 6 contains a 1 and row 4 says 16, then I want to shift this variable down by 16 cells from the base.

I want do this for each variable that has 1 in row 6 and ignore those with zeros. In the final output file, I always want to include Columns A and B and those series that have a 1 in row 6.

I have used the columns in yellow in the lags sheet as an example of what the final output sheet would look like in the sheet called "Example Output" (assuming that only three of the variables had a 1 in row 6). Ideally I want to create a new sheet with the final varaibles rather than overwriting the raw datasheet.

I’m suppose this would just involve some kind of conditional copying and pasting and so would be quite simple to do?

Thanks

Hamond

[mdmackillop]

Try this
[vba]
Option Explicit
Sub Macro1()
Dim ws As Worksheet, i As Long
Application.ScreenUpdating = False
Sheets("Raw").Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
ws.Name = "Output"
Sheets("Lags").Rows(4).Copy ws.Cells(2, 1)
Sheets("Lags").Rows(6).Copy ws.Cells(3, 1)
With ws
For i = 21 To 3 Step -1
If .Cells(3, i) = 0 Then
.Columns(i).Delete
Else
.Cells(4, i).Resize(.Cells(2, i)).Insert shift:=xlDown
End If
Next
.Rows("2:3").ClearContents
End With
Application.ScreenUpdating = True
End Sub

[/vba]

[Hamond]

This works great.

Thanks,

Hamond

[Hamond]

Hello,

Sorry I know I closed this post but I tried running the code on a new dataset and I'm getting a Application defined or object defined error and I can't understand why so I need to re-open.

Debugging takes me to the following Line.

.Cells(4, i).Resize(.Cells(2, i)).Insert shift:=xlDown

I have included the code and the results from running the macro in the output sheet in the attachment.

Hoping this will be simple to fix.

Thanks,

Hamond

[mdmackillop]

[VBA]Option Explicit
Sub Macro1()
Dim ws As Worksheet, i As Long
Application.ScreenUpdating = False
Sheets("Raw").Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
ws.Name = "Output"
Sheets("Lags").Rows(4).Copy ws.Cells(2, 1)
Sheets("Lags").Rows(6).Copy ws.Cells(3, 1)
With ws
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 3 Step -1
If .Cells(3, i) = 0 And Len(.Cells(3, i)) > 0 Then
.Columns(i).Delete
Else
If .Cells(2, i) > 0 Then
.Cells(4, i).Resize(.Cells(2, i)).Insert shift:=xlDown
End If
End If
Next
.Rows("2:3").ClearContents
End With
Application.ScreenUpdating = True
End Sub
[/VBA]