Solved

Compare 2 Columns and Ouput Matches

Posted on 2013-06-16
9
287 Views
Last Modified: 2013-06-18
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>>
0
Comment
Question by:tracyms
  • 4
  • 3
  • 2
9 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39251575
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
0
 

Author Comment

by:tracyms
ID: 39251623
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
0
 
LVL 80

Expert Comment

by:byundt
ID: 39251890
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39252414
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
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 80

Expert Comment

by:byundt
ID: 39252916
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
0
 
LVL 80

Accepted Solution

by:
byundt earned 300 total points
ID: 39253044
If you would also like to include the course number in the matching conditions, then here is a modified version of my last macro that does so. If the name matches, but the course doesn't, then it isn't listed in the message box.
Sub CourseMatcher()
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))
    Set rgRegistered = rgRegistered.Offset(0, -1).Resize(, 3)
End With
With Worksheets("Enrollments")
    Set rgEnrolled = .Range("B2")     'First name in list
    Set rgEnrolled = Range(rgEnrolled, .Cells(.Rows.Count, rgEnrolled.Column).End(xlUp))
    Set rgEnrolled = rgEnrolled.Offset(0, -1).Resize(, 3)
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, rgRegistered.Columns(3), rw.Cells(1, 3).Value) > 0 Then
        sFirstLast = sFirstLast & vbLf & rw.Cells(1, 1).Value & " " & rw.Cells(1, 2).Value & " " & rw.Cells(1, 3).Value
    ElseIf Application.CountIfs(rgRegistered.Columns(1), rw.Cells(1, 1).Value, rgRegistered.Columns(3), rw.Cells(1, 3).Value) > 0 Then
        sLast = sLast & vbLf & rw.Cells(1, 1).Value & " " & rw.Cells(1, 2).Value & " " & rw.Cells(1, 3).Value
    ElseIf Application.CountIfs(rgRegistered.Columns(1), rw.Cells(1, 1).Value, rgRegistered.Columns(2), rw.Cells(1, 2).Value) > 0 Then
        sFirst = sFirst & vbLf & rw.Cells(1, 1).Value & " " & rw.Cells(1, 2).Value & " " & rw.Cells(1, 3).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
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 200 total points
ID: 39253064
As byundt noted, I believe you have conflicting info here as in your intital post you mention the need to be advised on people who registered and enrolled in first and last name then you mention that sometimes people would register on a name then change name (maiden name) ... then on your post where you simplified it and attached the workbook like I requested you clearly mentioned that you needed a match of First AND Last name in both Enrolled and Registered. the file I previously attached took this into consideration.

Now to give you all possible alternatives ie
First and Last matching
First only matching
Last only matching

here is the new version and you may choose which one suits you best.
Regards
gowflow
FirstLast.xlsm
0
 

Author Closing Comment

by:tracyms
ID: 39254787
byundt - I like the option of including the courses, nice touch.
gowflow - I liked yours too - gave me what I asked.

Great job guys, thanks!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39255869
your welcome
gowflow
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now