How do I compare two sheets and output a report?
 

Scenario:
Sheet 1 has 500 entries with the columns Last Name, First Name, Email Address
Sheet 2 has 3000 entries with the columns Buddy Name, Last Name, First Name,
IM Platform

Problem:
I need to compare Sheet 1 against Sheet 2 for matches between the First and
Last name. Where it finds a match I need to correlate the Buddy Name with
that person in some fashion (i.e. say on Sheet 3 output Rob Smith,
, hotdog23, MSN)

Any help would be greatly appreciated

How do I compare two sheets and output a report?
 

Somethign like this should work (or get you very close):
Sub Match()

r1 = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
r2 = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Set r3 = Worksheets("sheet1")
Worksheets("sheet2").Range("B2").Select
For a = 2 To r2
For i = 2 To r1
If Cells(a, "A") = r3.Cells(i, "A") Then
temp = r3.Cells(i, "B")
te = te & "," & temp
Else
End If
Next i
Cells(a, "B") = te
te = ""
Next a
End Sub


Sub Match2()
Dim myCon As String
Dim myCell As Range
Dim cell As Range
For Each cell In Sheet2.Range("C2:C10")

myCon = ""
For Each myCell In Sheet1.Range("B2:B15")

If cell.Value < "" Then

If cell = myCell Then
If myCon = "" Then

myCon = myCell.Offset(0, 1) & ", " & myCell.Offset(0, -1)
Else
myCon = myCon & ", " & myCell.Offset(0, 3)

End If
End If
End If
Next myCell
cell.Offset(0, 3) = myCon
Next cell

End Sub

Backup your data before running this macro!! This macro, or any macro out
there, could product unintended consequences; hate to see you lose valuable
data...

HTH,
Ryan--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Paresh" wrote:

Scenario:
Sheet 1 has 500 entries with the columns Last Name, First Name, Email Address
Sheet 2 has 3000 entries with the columns Buddy Name, Last Name, First Name,
IM Platform

Problem:
I need to compare Sheet 1 against Sheet 2 for matches between the First and
Last name. Where it finds a match I need to correlate the Buddy Name with
that person in some fashion (i.e. say on Sheet 3 output Rob Smith,
, hotdog23, MSN)

Any help would be greatly appreciated

How do I compare two sheets and output a report?
 

Hi Ryan,

Thank you very much for your response. A couple of questions:

1. How/Where do I paste your code in Excel? How do I execute it?
2. The 4th line you have Set r3 = Worksheets("sheet1"). Does this variable
refer to the output? If so should it be "sheet3"?

"ryguy7272" wrote:

Somethign like this should work (or get you very close):
Sub Match()

r1 = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
r2 = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Set r3 = Worksheets("sheet1")
Worksheets("sheet2").Range("B2").Select
For a = 2 To r2
For i = 2 To r1
If Cells(a, "A") = r3.Cells(i, "A") Then
temp = r3.Cells(i, "B")
te = te & "," & temp
Else
End If
Next i
Cells(a, "B") = te
te = ""
Next a
End Sub


Sub Match2()
Dim myCon As String
Dim myCell As Range
Dim cell As Range
For Each cell In Sheet2.Range("C2:C10")

myCon = ""
For Each myCell In Sheet1.Range("B2:B15")

If cell.Value < "" Then

If cell = myCell Then
If myCon = "" Then

myCon = myCell.Offset(0, 1) & ", " & myCell.Offset(0, -1)
Else
myCon = myCon & ", " & myCell.Offset(0, 3)

End If
End If
End If
Next myCell
cell.Offset(0, 3) = myCon
Next cell

End Sub

Backup your data before running this macro!! This macro, or any macro out
there, could product unintended consequences; hate to see you lose valuable
data...

HTH,
Ryan--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Paresh" wrote:

Scenario:
Sheet 1 has 500 entries with the columns Last Name, First Name, Email Address
Sheet 2 has 3000 entries with the columns Buddy Name, Last Name, First Name,
IM Platform

Problem:
I need to compare Sheet 1 against Sheet 2 for matches between the First and
Last name. Where it finds a match I need to correlate the Buddy Name with
that person in some fashion (i.e. say on Sheet 3 output Rob Smith,
, hotdog23, MSN)

Any help would be greatly appreciated