Solved: merging worksheets matched on 'key' in column

[tpoynton]

Greetings,

I'm probably doing something silly, ignorant, inefficiently, or perhaps all of them! Feel free to make suggestions of any sort, even completely different solutions that have the same end result.

I'm trying to merge two worksheets based on a 'key' value. If that value does not exist in the 'School-level-ALL', put 'not moved' in the NCES sheet and move on to the next row; otherwise, copy the row from NCES to School-level-ALL and write 'moved' in the NCES sheet.

The data are sorted, if that helps.

The attached example has 11 or so rows; the original data have nearly 2000 rows.

I run into a problem in the first case where the key variable (iOrgCode in the sample) does not equal what is in the NCES sheet.

Here's the VBA (which is also in the attached workbook)

[vba]
Public Sub moveNCESdata()
Application.ScreenUpdating = False

Dim iOrgCode As Integer
Dim i As Integer
Dim cell As Range
Dim NCESrange As Range
Dim shtName As String
shtName = "NCES"

With Sheets(shtName)
Set NCESrange = Range(Cells(2, 1), Cells(11, 1))
End With

For i = 2 To 11

iOrgCode = Sheets("School-level-ALL").Cells(i, 4).Value

For Each cell In NCESrange

If iOrgCode = cell.Value Then
Sheets("NCES").Range(Cells(cell.Row, 1), Cells(cell.Row, 12)).Copy
Sheets("NCES").Cells(cell.Row, 13).Value = "MOVED"
Sheets("School-level-ALL").Cells(i, 39).PasteSpecial (xlPasteValues)
ElseIf iOrgCode <> cell.Value Then
Sheets("NCES").Cells(cell.Row, 13).Value = "NOT MOVED"
End If
Next cell

Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
[/vba]

EDIT - also, the routine didnt even work consistently in terms of copying data when the key did match...sometimes it did, sometimes it didnt...

[Bob Phillips]

Here is an alternative, but isn't the move logic back to front?

[vba]

Public Sub moveNCESdata()
Application.ScreenUpdating = False

Dim i As Long
Dim j As Long

Dim shtName As String
shtName = "NCES"

With Worksheets("School-level-ALL")

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

End With

With Worksheets(shtName)

For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

If IsError(Application.Match(.Cells(i, "A").Value, Worksheets("School-level-ALL").Columns(4), 0)) Then

.Cells(i, 13).Value = "NOT MOVED"
Else

.Cells(i, 13).Value = "MOVED"
.Rows(i).Copy
j = j + 1
Worksheets("School-level-ALL").Cells(j, "A").PasteSpecial xlPasteValues
End If

Next i
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
[/vba]

[tpoynton]

Thanks Bob! I was able to get things working, but alas, a poor problem description got in the way. I was determined to figure it out once you got me started...and yes, looping backwards (if that is what you meant) seems to have helped.

below is the code I ended up using. While I initially thought that the shorter list would have all values contained in the longer list, that wasnt the case. This merges the data well.

ALSO, your code merged the data by adding rows...my bad. I wanted to have the data added as columns. That was the easy part to fix!

[vba]
Public Sub BobsMoveNCESdata()
Application.ScreenUpdating = False

Dim i As Long
Dim j As Long

Dim shtName As String
shtName = "NCES"

With Worksheets("School-level-ALL")

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

End With

With Worksheets(shtName)

For i = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1

If IsError(Application.Match(.Cells(i, "A").Value, Worksheets("School-level-ALL").Columns(4), 0)) Then

.Cells(i, 13).Value = "NOT MOVED"
'Worksheets("School-level-ALL").Columns(4).Value
Else

.Cells(i, 13).Value = "MOVED"
.Range(.Cells(i, 1), .Cells(i, 12)).Copy

DoUntilFound:
If .Cells(i, 1) = Worksheets("School-level-ALL").Cells(j, 4) Then
Worksheets("School-level-ALL").Cells(j, "AM").PasteSpecial xlPasteValues
j = j - 1
Else
j = j - 1
GoTo DoUntilFound
End If
End If

Next i
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
[/vba]

as you can see, I struggled with it for a little while, but that's all good! now hopefully I wont make the same mistake for a month or two. and, I learned what application.match does!