Compare a column of data on one ws to another ws and append non-match data

[psrs0810]

I have two worksheets - LaborDetail and ECodes. In column E of LaborDetail, I want to compare that information to column B in ECodes. if there is not a match, to put that code at the bottom of the table of ECodes, column B.

Thanks

[austenr]

without sample data it would be hard to give a good correct answer. there is a good reference on how to do this here

http://www.mbaexcel.com/excel/how-to...t-match-match/

It uses index/match and gives a lot of scenarios. Much better than any VLOOKUP and is more efficient. JMHO.

[Leith Ross]

Hello psrs0810,

Here is a VBA solution to your problem. Add a new VBA Module to your Workbook and paste this code into it. This macro starts in row 2 of both worksheets. If your starting rows are different change them before you run the macro. The two addresses are marked.

Sub AddUniqueData()


    Dim Dict        As Object
    Dim Key         As Variant
    Dim RngBeg      As Range
    Dim rngECodes   As Range
    Dim rngLabor    As Range
    
        Set rngLabor = Worksheets("Labor").Range("E2")        ' <<<<<< CHANGE this address
            Set RngEnd = Worksheets("Labor").Cells(Rows.Count, "E").End(xlUp)
            If RngEnd.Row > rngLabor.Row Then
                Set rngLabor = rngLabor.Resize(RngEnd.Row - rngLabor.Row + 1, 1)
            End If
        
        Set rngECodes = Worksheets("ECodes").Range("B2")        ' <<<<<< CHANGE this address
            Set RngEnd = Worksheets("Labor").Cells(Rows.Count, "E").End(xlUp)
            If RngEnd.Row > rngECodes.Row Then
                Set rngECodes = rngECodes.Resize(RngEnd.Row - rngECodes.Row + 1, 1)
            End If
            
        Set Dict = CreateObject("Scripting.Dictionary")
            Dict.CompareMode = vbTextCompare
            
            ' Load ECodes into the Dictionary
            For Each Key In rngECodes
                Key = Trim(Key)
                If Not Dict.Exists(Key) Then
                    Dict.Add Key, True
                End If
            Next Key
            
            ' Add missing Labor Details to ECodes
            For Each Key In rngLabor
                Key = Trim(Key)
                If Not Exits.Dict(Key) Then
                    Dict.Add Key, True
                End If
            Next Key
            
        ' Output the updated ECodes
        rngECodes.Cells(1, 1).Resize(Dict.Count, 1).Value = Application.Transpose(Dict.Keys)
            
End Sub

[psrs0810]

Austenr - thanks, but I am looking to do this with VBA
Leith - I get an error on the first line.
The worksheet is LaborDetail, which I changed the respective line, but I am not able to get it to work.

[psrs0810]

I was able to get it to work - however, instead of adding any unfound codes to the bottom of the Ecode WS, it rewrote all of my codes.

[Leith Ross]

Hello psrs0810,

If you can post a copy of your workbook it will help me eliminate errors with the macro. If you can not post it here then maybe you could a public file sharing site and post the link. As a final option, you could email it to me.

[psrs0810]

How do I attach a file?
I see under Posting Permissions, that I can post an attachment, but I do not know where/how.
sorry - I am new to this site.
Thanks

[Leith Ross]

Hello psrs0810,

If you can not post it here then maybe you could a public file sharing site and post the link. As a final option, you could email it to me.