Compare 2 columns of names and report

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

5 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Oct 21, 2010 at 05:35 AM
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
0
Thanks a million for the quick reply! Both columns are in the same worksheet.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Oct 21, 2010 at 05:41 AM
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"
0
It ran with no errors, but it did not send any of the matching names into column C, any ideas?
0

Didn't find the answer you are looking for?

Ask a question
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Oct 21, 2010 at 07:41 AM
the required information is copied to sheet 3. anyhow I shall modify the macro tomorrow morning
0