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

jirpel

New Member
Joined
Jan 28, 2019
Messages
9
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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.
v4KkLL4L

Capture.png
[/URL][/IMG]
 
Upvote 0
I thought that might be what you wanted: try this:
Code:
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
 
Upvote 0
THANK YOU! This worked Perfect!!!. Wish I knew more VBA but I do follow what you are doing here. Your help is much appreciated.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top