Comparing Names using Excel

June 8, 2009 at 11:20:36
Specs: Windows XP
Hi - I have two worksheets,students and employees. I want to find out what employees are not students.


See More: Comparing Names using Excel

Report •

June 8, 2009 at 11:33:08
There are multiple ways of doing that, starting with a VLOOKUP and going all the way to a macro.

We would need to know a little more about the layout of your sheets before we could make any specific suggestions.

Why not read the Help Files on VLOOKUP and see if that function will produce the results you want.

Report •

June 8, 2009 at 11:56:15
Wow you are quick!

Ok.. so here it goes..I need to find out what employee doesn't have an account on a system , in this case and LMS (learning management system, hence students) and which student still exists in the LMS but is no longer an employee.

I can get a list of employees in an .xls formatt with the following information:

A = Last Name
B= First Name
C = Department Code
E= Employee ID
F= Title

I can get a list of students from the LMS system in an xls format with the following information

A= Last Name
B= First Name

I want to be able to tell something (excel, powershell, macro.. whatever) to look at the names on the employees list compare them to the student list and tell me who is not on the student list but is on the employee list.

Then look at the student list and compare them to the employee list and tell me who is there (student list but not employee)?

Make sense?

thanks for your prompt reply :)

Report •

June 8, 2009 at 12:25:46
Unless you are familiar with VBA, I think the simplest way would be to use a VLOOKUP function as I mentioned earlier.

Since it appears you need to be concerned with both first names and last names, I suggested you add a column where you concatenate these values on both sheets so that the VLOOKUP has a common value to look up.

For example, on the employee sheet (Sheet1), assuming your names start in row 1, you could put this in G1 and drag it down:

=A1&B1 to get SmithJoe

Do the same thing in Column C of the Student sheet (Sheet2).

Then in Sheet1!H1 enter this and drag it down:

=IF(ISERROR(VLOOKUP(G1,Sheet2!$C$1:$C$10,1,0)),"Not A Student", "A Student")

In Sheet2!D2, enter this and drag it down:

=IF(ISERROR(VLOOKUP(C1,Sheet1!$G$1:$G$10,1,0)),"Not An Employee", "An Employee")

Obviously you'll need to adjust your ranges to match the lengths of your list.

Basically what will happen is that if the VLOOKUP function returns an error because it didn't find the concatenated value from one sheet in the list of concatenated values in the other sheet, then the IF statement will be TRUE and return the "Not A..." portion.

If it finds the value, then there will be no error and the IF statement will be FALSE, returning the "A Student" or "An Employee" answer.

Report •

Related Solutions

June 8, 2009 at 12:46:46
WOW.. That was awesome!!! Thank you :)

I have a very hard time understanding excel! but you made it very easy.

Do you have any recommendations on how to automate this? I would be willing to try anything :)


Report •

June 8, 2009 at 13:22:53
It could be automated by using VBA, but it would help know how often you would be doing this comparison. Sometimes cutting and pasting the formulae is easier than writing (and maintaining) the VBA code.

Report •

June 8, 2009 at 19:00:32
I was bored...

This code will automate the placement of the formulae in Sheet1 Columns G & H and Sheet2 Columns C & D.

It will also hide the columns with the concatenated names.

Sub AutoFormula()
'Find last cell in Sheet1
  lRowSht1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Find last cell in Sheet2
  lRowSht2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row

'Insert formula in Sheet 1
  For nxtName = 1 To lRowSht1
   Sheets(1).Cells(nxtName, 7).Formula = "=A" & nxtName & "&B" & nxtName
   Sheets(1).Cells(nxtName, 8).Formula = "=IF(ISERROR(VLOOKUP(G" & nxtName & _
        ",Sheet2!$C$1:$C$" & lRowSht2 & ",1,0)), ""Not A Student"", ""A Student"")"
'Hide concatenated names
    Sheets(1).Columns(7).Hidden = True
'Insert formula in Sheet 2
  For nxtName = 1 To lRowSht2
   Sheets(2).Cells(nxtName, 3).Formula = "=A" & nxtName & "&B" & nxtName
   Sheets(2).Cells(nxtName, 4).Formula = "=IF(ISERROR(VLOOKUP(C" & nxtName & _
        ",Sheet1!$G$1:$G$" & lRowSht1 & ",1,0)), ""Not An Employee"", ""An Employee"")"
'Hide concatenated names
    Sheets(2).Columns(3).Hidden = True
End Sub

Report •

Ask Question