De-Duping and Consolidating--Possible?

[Sarah Dahl]

I have a spread of ~1000 users with emails, and a separate spread of ~500 of those same users without the email addresses affiliated. Before I do it manually, I thought I'd check with the geniuses here to see if there was a way to consolidate the records, or rows. Please see attached, one sheet is the ~1000 and the other is the ~500 who need the email addresses in the same row, if that makes sense.

Thanks in advance!

 

[jindon]

VBA solution, if you like.

Sub test()
    Dim a, i As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    Sheets("kjcopy").Cells.Replace ChrW(160), ""
    With Sheets("kjcopy").Cells(1).CurrentRegion
        .Replace ChrW(160), "", 2: a = .Value
    End With
    For i = 2 To UBound(a, 1)
        a(i, 1) = Replace(a(i, 1), ChrW(160), "")
        If Trim$(a(i, 4)) <> "" Then dic(Trim$(a(i, 1))) = a(i, 4)
    Next
    With Sheets("sdcopy").Cells(1).CurrentRegion.Resize(, 4)
        .Replace ChrW(160), "", 2: a = .Value
        For i = 2 To UBound(a, 1)
            a(i, 4) = dic(Trim$(a(i, 1)))
        Next
        .Value = a: .Parent.Select
    End With
End Sub