Help with VB Script Compare and Update

[udprocks]

Good Morning All,

1. Compare the sheet UPDATE and MASTER. If there are NEW values (based on ID column) then paste those to the end of the MASTER sheet. If there are items MISSING in UPDATE but are found in MASTER I want the ID value in MASTER to have an "x" put on the front of the value. IE - if the value in master was 1234 and the value is MISSING in UPDATE then the value in MASTER will now be "x1234".

Now the master should contain all new values and all old values will be preserved with an "x" in front of the original number.

2,. Now for those values where we added an "x" I want the values in the DASHBOARD sheet to be changed from 1234 to "x1234" so the INDEX lookup will find the new value.

I am attaching 2 files. One is ORIGINAL before the script runs. The second is UPDATE which is the desired result AFTER the script runs.

[jindon]

Try

Sub test()
    Dim a, i As Long, e, n As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    a = Sheets("update").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        dic(a(i, 4)) = Application.Index(a, i, 0)
    Next
    With Sheets("master").Cells(1).CurrentRegion
        a = .Value
        For i = 2 To UBound(a, 1)
            If dic.exists(a(i, 4)) Then
                If dic(a(i, 4))(1) = "" Then
                    dic(a(i, 1)) = Empty
                    a(i, 1) = "x" & a(i, 1)
                End If
                dic.Remove a(i, 4)
            End If
        Next
        .Value = a
        For Each e In dic
            If IsArray(dic(e)) Then
                n = n + 1
                .Rows(.Rows.Count + n).Value = dic(e)
                dic.Remove e
            End If
        Next
    End With
    If dic.Count = 0 Then Exit Sub
    With Sheets("dashboard").[a2].CurrentRegion
        For i = 2 To UBound(a, 1)
            If dic.exists(.Cells(i, 4).Value) Then .Cells(i, 4).Value = "x" & .Cells(i, 4).Value
        Next
    End With
End Sub