Match up data

[maninjapan]

Im trying to match up some stock data from 2 different products, but there are a few gaps along the way. Id like to create a macro that will go through and match things up. The rules would be as follows

Right side will be matched up to the left side,
If the right side has an extra day the cell will be deleted and everything matched up

If the left side has an extra day the day will be created on the right side and the previous days prices will be copied down.

After this is completed each row of data will match dates.

Has anyone seen a macro that does something similar to this?
Ive attached some of the data I want to match up.

[maninjapan]

just wondering if anyone had an idea on this one?

Thanks

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
j = 1
Do

For i = j To LastRow

If .Cells(i, "A").Value2 < .Cells(i, "D").Value2 Then

.Cells(i, "D").Resize(, 3).Insert shift:=xlShiftDown
.Cells(i, "D").Value = .Cells(i, "A").Value2
.Cells(i - 1, "E").Resize(, 2).Copy .Cells(i, "E")
j = i
Exit For
End If
Next i
Loop Until i > LastRow
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[maninjapan]

Thanks xld, itr does the 2nd part, add the missing data, perfect (Ive adjusted the columns to match the data I have now).

Ive tried to add the first part, delete the cells on the right if there is an extra line of data in the right side but getting a compiling error. Here it is as Ive adjusted it.
[VBA]
Public Sub Match()
Dim i As Long, j As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
j = 1
Do

For i = j To LastRow

If .Cells(i, "B").Value2 < .Cells(i, "G").Value2 Then

.Cells(i, "F").Resize(, 6).Insert shift:=xlShiftDown
.Cells(i, "G").Value = .Cells(i, "B").Value2
.Cells(i, "F").Value = .Cells(i, "A").Value2
.Cells(i - 1, "H").Resize(, 2).Copy .Cells(i, "H")

Else
If .Cells(i, "B").Value2 > .Cells(i, "G").Value2 Then
.Cells(i, "F").Resize(, 6).Insert shift:=xlShiftUp
j = i

Exit For
End If
Next i
Loop Until i > LastRow
End With

Application.ScreenUpdating = True
End Sub[/VBA]

Am I at least close?

[Bob Phillips]

[vba]

Public Sub Match()
Dim i As Long, j As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
j = 1
Do

For i = j To LastRow

If .Cells(i, "B").Value2 < .Cells(i, "G").Value2 Then

.Cells(i, "F").Resize(, 6).Insert shift:=xlShiftDown
.Cells(i, "G").Value = .Cells(i, "B").Value2
.Cells(i, "F").Value = .Cells(i, "A").Value2
.Cells(i - 1, "H").Resize(, 2).Copy .Cells(i, "H")
Exit For

ElseIf .Cells(i, "B").Value2 > .Cells(i, "G").Value2 Then

.Cells(i, "F").Resize(, 6).Delete shift:=xlShiftUp
Exit For
End If
Next i
Loop Until i > LastRow
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[maninjapan]

Thanks xld, still seems to be doing something wrong though... Not sure exactly how to explain it, but after running the macro there seems to be some excess cells in the right hand side data.

[Bob Phillips]

Where is it wrong, it looks fine to my eyes.

[Aussiebear]

After running the sub, there are 3 additional lines in the right hand column and cells J11, J27, J41 & J42 are blank.

[maninjapan]

Yep, looking at the data, these cells should probably have been deleted as they werent in the lefthand side data. Instead they seem to have been grouped down the bottom. It looks like I can probably run the macro then just delete the excess cells that have been pushed down the bottom though...

[Bob Phillips]

Okay, I thought that was the results after running, not before. The rows inserted at 11 and 27 are perfectly correct, that is what the code is meant to do.

Matching code like this is, has to be, predicated upon the assumption that the data is sorted by the key field, otherwsie the matching is meaningless. This data now has two items at the end out of order, this throws it completely. If those last two rows are removed, it works fine apart from not tidyoing up the second block as it now extends beyond our last row. That can easily be accomodated, but first you have to resolve the order issue, it cannot work with unsorted key data.

[maninjapan]

Sorry, I should have been clearer when I posted that data. I can see the issue occurs when the macro is run. The data isnt out of order before the macro is run.....

[maninjapan]

so basically what Im looking to do here is more complicated than I thought?

[Bob Phillips]

Yes, unless sorting first is an option.