compare 2 columns of names and report Closed

Question

Hello all, I have an excel spreadsheet with 2000 names (lastname, firstname) in column A, in Column B I have another 1500 names (lastname,firstname). I need to compare the 2 columns and find the names which exist in both columns A and B, and send them to Column C. I am crap at excel and cant get any of the VLOOKUPS or macros that I have tried to work. Any suggestions would be very greatly apprecaited. Kind regards, -Jim

venkat1926 · Answer

in sheet1 and sheet2 row 1 is having row headings. 

in that case try this mcro 


Sub test()
Dim r As Range, c As Range, dest As Range
Dim cfind As Range, x As String
With Worksheets("sheet1")
Set r = Range(.Range("c2"), .Range("c2").End(xlDown))
For Each c In r
x = c.Value
With Worksheets("sheet2").Columns("c:c")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole, LookIn:=xlValues)
If cfind Is Nothing Then GoTo nextc
cfind.EntireRow.Copy
With Worksheets("sheet3")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest.PasteSpecial
End With
End With
nextc:
Next c
End With


End Sub

traveller20 · Answer

Thanks a million for the quick reply! Both columns are in the same worksheet.

venkat1926 · Answer

SORRY I forgot to add one important instruction 
in sheet 1 and sheet 2 in c2 type the formula 

=A2&B2

copy C2 down in both the sheets.

THEN ONLY RUN THE MACRO "TEST"

traveller20 · Answer

It ran with no errors, but it did not send any of the matching names into column C, any ideas?

venkat1926 · Answer

the required information is copied to sheet 3.  anyhow I shall modify the macro tomorrow morning

Compare 2 columns of names and report

5 responses

Subscribe To Our Newsletter!