asked on

Compare 2 Columns and Ouput Matches

I'd like to campare first name (column A) and Last Name (column B) on sheet 1 (named Registrations) to first name (column A) and Last Name (column B) on
sheet 2 (named Enrollments).

If a first and/or last name is found on sheet 1 that matches sheet 2, then output the sheet 2 match (first and last name) in a message box.

Some background:

On sheet 1, I import names (to an excel spreadsheet) from people who've registered for a course - this comes directly from our registration system. The people who
registered will then need to also enroll at an online website to access their classroom. I keep track of both because I can't give them access
to their classroom unless they've registered.

They can enroll at the online website before they register but I will need to drop them from the online website if they don't register by the
course start date. I keep a list of people who enrolled at the website and didn't register on a seperate sheet. I'd like to know if the people
who enrolled at the online website are in the registration list that I import - without having to go back and look at it as I'm doing now.

I've noticed that some people are using different last names (maiden or married) when they enroll at the online website (this is because they don't update their
information in our registration system - we use unique IDs for users so unless they specify a change we look for the ID and process the
registration). This happens for returning users who have registered for a course before and didn't update their information.

This is why I'd like to compare the first name as well as last name since the first name may match but the last name may not. I hope this makes sense, thanks!

<<Question retitled and Visual Basic Class & Misc. Programming dropped from topic list--byundt, Microsoft Excel Zone Advisor 6/17/2013>>

Jacques Geday

can you post a sample workbook with some data there ? then we can build the macro accordingly.

Just a note you say a messagbox if they match !!! not sure this is a good way as say you have 1000 records in both sheet isn't the macro supposed to loop for all records and see where they match in both sheets ? or I got what you want wrongly ??? as then you would have some say 300 messagboxes that will just scroll one after the other ???

gowflow

tracyms

ASKER

I will simplify:

I've attached a sample spreadsheet. If the first and last name in the Registrations spreadsheet is found in the Enrollments spreadsheet then show one message box listing the first and last name of that person (or more if more than one) from Enrollments spreadsheet.

I normally have about 100 registrations or less per month so I won't have more than that on the Registrations spreadsheet. I normally have 12 or less on the enrollments spreadsheet per month. There won't be a lot of records to go through.

I entered the same name in both sheets to give a start. I highlight registrations in green when they register and enroll.
Test.xlsx

byundt

Here is a macro that matches your enrollees and registrations. Install it in a regular module sheet and run it with any sheet in the workbook being active. The macro will search columns B and C on worksheet Registrations for matches from worksheet Enrollments columns B and C.

Sub Matcher()
Dim rgEnrolled As Range, rgRegistered As Range, rw As Range
Dim s As String
With Worksheets("Registrations")
    Set rgRegistered = .Range("B2")     'First name in list
    Set rgRegistered = Range(rgRegistered, .Cells(.Rows.Count, rgRegistered.Column).End(xlUp)).Resize(, 2)
End With
With Worksheets("Enrollments")
    Set rgEnrolled = .Range("B2")     'First name in list
    Set rgEnrolled = Range(rgEnrolled, .Cells(.Rows.Count, rgEnrolled.Column).End(xlUp)).Resize(, 2)
End With
For Each rw In rgEnrolled.Rows
    If Application.CountIfs(rgRegistered.Columns(1), rw.Cells(1, 1).Value, rgRegistered.Columns(2), rw.Cells(1, 2).Value) > 0 Then
        s = s & vbLf & rw.Cells(1, 1).Value & " " & rw.Cells(1, 2).Value
    End If
Next
If s <> "" Then
    s = "The following enrolled people have registered:" & vbLf & Mid(s, 2)
Else
    s = "None of the enrollees have registered"
End If
MsgBox s
End Sub

Open in new window

Brad

Jacques Geday

Hi sorry for late reply ystday was sunday !!! :)

Load the file enable macros and press on the button Enrolled and Registered (I have added some tricky data just to make sure)

Let me know.
gowflow
FirstLast.xlsm

byundt

After rereading the question, I now see that you have three possible matching conditions: both first and last names match, first names only match, and last names only match. I modified my macro to keep track of all three possibilities and attached a sample workbook.

Sub Matcher()
Dim rgEnrolled As Range, rgRegistered As Range, rw As Range
Dim sFirst As String, sLast As String, sFirstLast As String
With Worksheets("Registrations")
    Set rgRegistered = .Range("B2")     'First name in list
    Set rgRegistered = Range(rgRegistered, .Cells(.Rows.Count, rgRegistered.Column).End(xlUp)).Resize(, 2)
End With
With Worksheets("Enrollments")
    Set rgEnrolled = .Range("B2")     'First name in list
    Set rgEnrolled = Range(rgEnrolled, .Cells(.Rows.Count, rgEnrolled.Column).End(xlUp)).Resize(, 2)
End With
For Each rw In rgEnrolled.Rows
    If Application.CountIfs(rgRegistered.Columns(1), rw.Cells(1, 1).Value, rgRegistered.Columns(2), rw.Cells(1, 2).Value) > 0 Then
        sFirstLast = sFirstLast & vbLf & rw.Cells(1, 1).Value & " " & rw.Cells(1, 2).Value
    ElseIf Application.CountIf(rgRegistered.Columns(2), rw.Cells(1, 2).Value) > 0 Then
        sLast = sLast & vbLf & rw.Cells(1, 1).Value & " " & rw.Cells(1, 2).Value
    ElseIf Application.CountIf(rgRegistered.Columns(1), rw.Cells(1, 1).Value) > 0 Then
        sFirst = sFirst & vbLf & rw.Cells(1, 1).Value & " " & rw.Cells(1, 2).Value
    End If
Next
If sFirstLast <> "" Then
    sFirstLast = "The following enrolled people have registered:" & vbLf & Mid(sFirstLast, 2)
Else
    sFirstLast = "None of the enrollees have registered under both first & last names"
End If
MsgBox sFirstLast
If sFirst <> "" Then
    sFirst = "The following enrolled people may have registered--their first names match:" & vbLf & Mid(sFirst, 2)
Else
    sFirst = "None of the enrollees have registered under their first names"
End If
MsgBox sFirst
If sLast <> "" Then
    sLast = "The following enrolled people may have registered--their last names match:" & vbLf & Mid(sLast, 2)
Else
    sLast = "None of the enrollees have registered under their last names"
End If
MsgBox sLast
End Sub

Open in new window

NameMatchingQ28158655.xlsm

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jacques Geday

membership

This solution is only available to members.