Merge worksheets with unique key in common

Hi,

I have two worksheets with the same column headers and a unique key in common, Name. I need to merge the two to create a single worksheet with complete records.

The cases that will be encountered are:
- there will be exact duplicate records, of which i only want to keep one
- there will be unique records in each worksheet which i need to retain
- one worksheet will have blank fields that are filled in on the second worksheet. i need to merge the records to create a single complete record
- there will be cases where each worksheet has a record for Name and has field entries that do not match, in which case i need worksheet one to take precedence

Please see attached example with Worksheet 1, Worksheet 2, and Merged worksheet.

I've searched for days and can't find an answer on how to accomplish this. I thought about reading both tables into access but i have no experience with Access and when I try to bring them both in it doesn't like that the data is duplicated.

Can anyone help me?
Thanks!

I posted my question originally on this site: http://www.ozgrid.com/forum/showthre...rds+unique+key

I didn't receive any responses after four days, which is why I posted here. I tried to delete my other post, but had no luck.

It's important you mention when you post at other forums because we can then avoid the possibility of somebody spending time solving a problem which has already been solved elsewhere. You should have mentioned it at Ozgrid too.

I think this works for your example. It does assume the two sheets have the same number of columns.

Sub x()

Dim vOut(), vIn(), i As Long, j As Long, n As Long, oDic As Object, r1 As Long, r2 As Long, c As Long

With Sheets("Wk1")
    r1 = .Range("A1").CurrentRegion.Rows.Count - 1
    c = .Range("A1").CurrentRegion.Columns.Count
    vIn = .Range("A2").Resize(r1, c).Value
End With

r2 = Sheets("Wk2").Range("A1").CurrentRegion.Rows.Count - 1
ReDim vOut(1 To r1 + r2, 1 To c)
Set oDic = CreateObject("Scripting.Dictionary")

With oDic
    For i = 1 To UBound(vIn, 1)
        If Not .Exists(vIn(i, 1)) Then
            n = n + 1
            For j = 1 To UBound(vIn, 2)
                vOut(n, j) = vIn(i, j)
            Next j
            .Add vIn(i, 1), n
        End If
    Next i
End With

vIn = Sheets("Wk2").Range("A2").Resize(r2, c).Value

With oDic
    For i = 1 To UBound(vIn, 1)
        If Not .Exists(vIn(i, 1)) Then
            n = n + 1
            For j = 1 To UBound(vIn, 2)
                vOut(n, j) = vIn(i, j)
            Next j
            .Add vIn(i, 1), n
        Else
            For j = 2 To UBound(vIn, 2)
                If vOut(.Item(vIn(i, 1)), j) = "" And vIn(i, j) <> "" Then
                    vOut(.Item(vIn(i, 1)), j) = vIn(i, j)
                End If
            Next j
        End If
    Next i
End With

With Sheets("Merged").Range("A1")
    .Resize(, c).Value = Sheets("Wk1").Range("A1").Resize(, c).Value
    .Resize(, c).Font.Bold = True
    .CurrentRegion.Offset(1).ClearContents
    .Offset(1).Resize(n, c) = vOut
End With

End Sub