Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help with report output! | Excel Worksheet Functions | |||
how to compare two columns and get a output from it | Excel Worksheet Functions | |||
Compare data with in a couple of sheets then output to a blank sheet | Excel Programming | |||
report output format | Excel Programming | |||
Need help with report output....macro??? | Excel Programming |