Hi - I have two worksheets,students and employees. I want to find out what employees are not students.

???thanks

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.

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= TitleI can get a list of students from the LMS system in an xls format with the following information

A= Last Name

B= First NameI 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 :)

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.

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 :)

Thanks

K

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.

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"")" Next '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"")" Next 'Hide concatenated names Sheets(2).Columns(3).Hidden = True End Sub

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History