Compare 2 columns of names and report

Closed
traveller20 - 21 Oct 2010 à 05:09
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 21 Oct 2010 à 07:41
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
Related:

5 responses

venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
21 Oct 2010 à 05:35
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
21 Oct 2010 à 05:39
Thanks a million for the quick reply! Both columns are in the same worksheet.
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
21 Oct 2010 à 05:41
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
21 Oct 2010 à 05:45
It ran with no errors, but it did not send any of the matching names into column C, any ideas?
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
21 Oct 2010 à 07:41
the required information is copied to sheet 3. anyhow I shall modify the macro tomorrow morning