How can I check to see if two Excel spreadsheet columns have the same exact data?

I have 2 Excel spreadsheets that I must do a side by side comparison on. I am comparing one employee spreadsheet to another spreadsheet of the same employee and there are columns that need a 'Y' or 'N' in reference to things like "Does employee have access to this?".

The reason I am doing this is because the employee had a name change but still needs the same file access as before. Now that the employee has a new employee id, etc.... it is imperative that I be certain the new employee(same employee but different name) has the same access('Y' and 'N') as before, to their over 600 files.

I need to be absolutely certain I have a 'Y' or 'N' on that employee's access, and that their access to certain systems is the same as their previous name.

Is there a way to tell Excel to count up all the row values and see how many 'Y' or 'N' values there are? Or is there a way to have Excel do an actual comparison of one spreadsheet's values in a certain column to another spreadsheet with the same column?

Answer

If you're comparing row values to row values and the values are always the same (e.g. always Yes or No or True or False), its a relatively simple task to compare. Simply use the formula =A1=B1 to determine if the values are the same. You can fill this formula down for every row that needs the comparison. Then you can filter on the comparison column and select any rows that return a False.

EDIT: This will work across workbooks, just properly reference the cells e.g. =A1=[Book2]Sheet1!A1. You should also be able to do this for two columns from a third workbook.