PDA

View Full Version : First & Last name comparisons



Shaolin
06-12-2009, 07:07 AM
I would like to compare first and last names (column A and B, respectively) on sheet 1 with first and last names (columns C and D, respectively) on sheet 2 and print the matches (entire row) on sheet 3, starting on row 2 down. Please help

Public Sub MDC_Usage()
'*Use this Macro to compare names from two worksheets and print result in another
'Declaring variables
Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
Dim Nm As Range, c As Range, Tgt As Range
Dim Rng As Range

Set Sh1 = Sheets("Users")
Set Sh2 = Sheets("Usage")
Set Sh3 = Sheets("Final")

With Sh1
'Get list of last names from Sh1
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))

'Search those names in Sh2
With Sh2.Columns(3)
For Each Nm In Rng
Set c = .Find(Nm, LookIn:=xlValues)
If Not c Is Nothing Then
Set Tgt = Sh3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
'Copy names
c.Offset(, -2).Resize(, 70).Copy Tgt
End If
Next
End With
End With
End Sub

p45cal
06-12-2009, 09:23 AM
Not tested (and I've not declared variables):Public Sub MDC_Usage()
'*Use this Macro to compare names from two worksheets and print result in another
'Declaring variables
Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
Dim Nm As Range, c As Range, Tgt As Range
Dim Rng As Range

Set Sh1 = Sheets("Users")
Set Sh2 = Sheets("Usage")
Set Sh3 = Sheets("Final")

'Get list of last names from Sh1
Set Rng = Range(Sh1.Cells(2, 1), Sh1.Cells(Sh1.Rows.Count, 1).End(xlUp))
'Get list of last names from Sh2
Set Rng2 = Range(Sh2.Cells(1, 3), Sh2.Cells(Sh2.Rows.Count, 3).End(xlUp))

'Search those names in Sh2
For Each Nm In Rng
For Each Nm2 In Rng2
If Nm.Value = Nm2.Value And Nm.Offset(, 1).Value = Nm2.Offset(, 1).Value Then
Set Tgt = Sh3.Cells(Sh3.Rows.Count, 1).End(xlUp).Offset(1)
'Copy names
Nm2.EntireRow.Copy Tgt 'your code copied from Sh2, replace Nm2 with Nm if you want to copy from Sh1
End If
Next
Next
End Sub

georgiboy
06-12-2009, 10:38 AM
Have a look at this..

Hope this helps

George