Compare two columns on separate sheet, if missing, insert missing data

Solution 1

Public Sub CopyUsingSearch()
'# declare
   Dim xlsData As Worksheet
   Dim xlsTracker As Worksheet
   Dim rngDataCell As Range
'# initialise
   Set xlsData = ThisWorkbook.Worksheets("Data")
   Set xlsTracker = ThisWorkbook.Worksheets("Tracker")
'# copy
   With xlsData
      For Each rngDataCell In .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
         If xlsTracker.Range("A:A").Find(rngDataCell.Value, , , xlWhole) Is Nothing Then
            xlsTracker.Cells(xlsTracker.Rows.Count, "A").End(xlUp).Offset(1).Value = rngDataCell.Value
         End If
      Next rngDataCell
   End With
End Sub

For every value from the Data worksheet a search will be performed on the Tracker worksheet, and the entry will be added when not found. It works but may be slow when comparing a large number of values


Solution 2

Public Sub CopyUsingIndex()
'# declare
   Dim xlsData As Worksheet
   Dim xlsTracker As Worksheet
   Dim lngRowNumber As Long
   Dim lngTargetRow As Long
   Dim rngDataCell As Range
   Dim dctIndex As Object
'# initialise
   Set xlsData = ThisWorkbook.Worksheets("Data")
   Set xlsTracker = ThisWorkbook.Worksheets("Tracker")
   Set dctIndex = CreateObject("Scripting.Dictionary")
   dctIndex.CompareMode = 1
'# build index of existing values
   With xlsTracker
      lngTargetRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      For lngRowNumber = 1 To lngTargetRow
         If Not dctIndex.Exists(.Cells(lngRowNumber, "A").Value) Then
            dctIndex.Add .Cells(lngRowNumber, "A").Value, lngRowNumber
         End If
      Next lngRowNumber
   End With
'# copy data that does not exist as yet
   With xlsData
      For lngRowNumber = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
         If Not dctIndex.Exists(.Cells(lngRowNumber, "A").Value) Then
            lngTargetRow = lngTargetRow + 1
            xlsTracker.Cells(lngTargetRow, "A").Value = .Cells(lngRowNumber, "A").Value
         End If
      Next lngRowNumber
   End With
End Sub

Same concept but now using an keyed index (dictionary). More code but faster when processing large quantities

You should create a module in the VBA editor window. Use Alt-F11 to switch to the editor. I personally prefer solution 2

Please try

Public Sub CopyUsingIndex()
'# declare
   Dim xlsData As Worksheet
   Dim xlsTracker As Worksheet
   Dim lngRowNumber As Long
   Dim lngTargetRow As Long
   Dim rngDataCell As Range
   Dim dctIndex As Object
'# initialise
   Set xlsData = ThisWorkbook.Worksheets("Data")
   Set xlsTracker = ThisWorkbook.Worksheets("Tracker")
   Set dctIndex = CreateObject("Scripting.Dictionary")
   dctIndex.CompareMode = 1
'# build index of existing values
   With xlsTracker
      lngTargetRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      For lngRowNumber = 1 To lngTargetRow
         If Not dctIndex.Exists(.Cells(lngRowNumber, "A").Value) Then
            dctIndex.Add .Cells(lngRowNumber, "A").Value, lngRowNumber
         End If
      Next lngRowNumber
   End With
'# copy data that does not exist as yet
   With xlsData
      For lngRowNumber = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
         If Not dctIndex.Exists(.Cells(lngRowNumber, "A").Value) Then
            lngTargetRow = lngTargetRow + 1
            xlsTracker.Cells(lngTargetRow, "A").Value = .Cells(lngRowNumber, "A").Value
            xlsTracker.Cells(lngTargetRow, "B").Value = .Cells(lngRowNumber, "B").Value
         End If
      Next lngRowNumber
   End With
End Sub

add this line

xlsTracker.Cells(lngTargetRow, "A").NumberFormat = "@"

just before

xlsTracker.Cells(lngTargetRow, "A").Value = .Cells(lngRowNumber, "A").Value

effektz,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

I have data in two sheets. How do I use the code here to compare the data in second sheet with the first sheet and insert the missing data in the second sheet

Jane, did you miss the thread immediately above yours??

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread