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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.