Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
I want something like this type of comparison, the only difference being that I am referring to two different spreadsheets having the same column, not the same spreadsheet with different column – Shades Jul 9 '12 at 16:00
Hi @Shades, please have a look at this SuperUser question and see if any of its answers can help you with your problem (comparing columns). Tell us what worked and didn't work, or, if possible, include sample data. – Kaze Jul 9 '12 at 16:59
up vote 4 down vote accepted

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.

share|improve this answer
Will this work if it is two different excel files? At the moment I want to compare column 'E' in the first excel file containing "y" and 'n'values to column 'E' in the other excel file. The above can do exactly that for me? – Shades Jul 9 '12 at 16:47
You could copy and paste the data together into one sheet for the purposes of your test. – Brad Jul 9 '12 at 16:54

Mr. Vandenbos's answer also suppose the rows are in the same order. Ex.: File1 File2 File3

But if one of the list is not in the same order has the other like this: File3 File1 File2

Than you should use the VLOOKUP() Excel function, which performs a 'vertical lookup'. ... or sort first. I only wanted to make people aware of the VLOOKUP and HLOOKUP functions of Excel which are made for that purpose.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .