PDA

View Full Version : Compare a column of data on one ws to another ws and append non-match data



psrs0810
09-14-2017, 08:30 AM
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
09-14-2017, 10:11 AM
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-use-offset-match-match/

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

Leith Ross
09-14-2017, 10:21 AM
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
09-14-2017, 10:30 AM
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
09-14-2017, 10:50 AM
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
09-14-2017, 11:05 AM
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
09-14-2017, 11:37 AM
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
09-14-2017, 12:15 PM
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.