How to Compare two list and add missing information from List A to List B

[jirpel]

Looking for a little help. I need a way to compare a list on one sheet (A), Column A; with a list of values on another sheet (COMPARE), Column A.

If the value is missing from the list on Sheet (COMPARE), it copies the value to the bottom of the list on Sheet (A) along with the values of the two adjacent columns on Sheet (COMPARE).

I have found a VBA that will do the comparison and add the missing value to the end of the list but I do not know how to copy the adjacent column data over. I am by far not a VBA expert. My VBA code is below. Thanks in advance.

---

Sub x()


Dim r1 As Excel.Range
Dim r2 As Excel.Range
Dim r3 As Excel.Range
Dim c As Excel.Range

Dim x As Date

Set r1 = Range("A1").Resize(Range("A" & Rows.Count).End(xlUp).Row)
Set r2 = Worksheets("Compare").Range("A2").Resize(Worksheets("Compare").Range("A" & Rows.Count).End(xlUp).Row)

x = Now

For Each c In r2

Set r3 = r1.Find(What:=c.Value, MatchCase:=False, Lookat:=xlWhole)

If r3 Is Nothing Then

Range("A" & Rows.Count).End(xlUp).Offset(1).Value = c.Value

End If
Next

Debug.Print DateDiff("s", x, Now)

End Sub

 

[offthelip]

Your requirements are unclear:

If the value is missing from the list on Sheet (COMPARE), it copies the value to the bottom of the list on Sheet (A) along with the values of the two adjacent columns on Sheet (COMPARE).

if the value is missing from the sheet Compare, how can there be two adjacent columns on the sheet (compare)
If it wasn't for this, it is very easy to do

 

[jirpel]

Sorry - Got that backwards. Any value in the list on Sheet (COMPARE), Column A that is not listed on Sheet (A), Column A is copied to the last row on Sheet (A) along with two adjacent columns, Sheet (COMPARE), Columns B & C.

[/URL][/IMG]

 

[offthelip]

I thought that might be what you wanted: try this:

Sub test()
With Worksheets("Compare")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
compar = .Range(.Cells(1, 1), .Cells(lastrow, 3))
End With
Worksheets("A").Select
lastdata = Cells(Rows.Count, "A").End(xlUp).Row
datar = Range(Cells(1, 1), Cells(lastdata, 1))
indi = lastdata + 1


 For j = 1 To lastrow
  For i = 1 To lastdata
   fnd = False
   If datar(i, 1) = compar(j, 1) Then
    ' name found
     fnd = True
     Exit For
   End If
  Next i
  If Not (fnd) Then
      For kk = 1 To 3
       Cells(indi, kk) = compar(j, kk)
      Next kk
      indi = indi + 1
  End If
 Next j
 
End Sub

 

[jirpel]

THANK YOU! This worked Perfect!!!. Wish I knew more VBA but I do follow what you are doing here. Your help is much appreciated.