Find the differences between 2 Excel worksheets?

Tag: excel Author: fsandy100 Date: 2009-09-14

I have two excel files with the same structure: they both have 1 column with data. One has 800 records and the other has 805 records, but I am not sure which of the 5 in the 805 set are not in the 800 set. Can I find this out using Excel?

Note that if using a VLOOKUP solution you are assuming the values in your data are exclusive. For example, if the extra 5 rows' data repeats already existing values, then they won't be identified.

Best Answer

vlookup is your friend!

Position your column, one value per row, in column A of each spreadsheet. in column B of the larger sheet, type

=VLOOKUP(A1,'[Book2.xlsb]SheetName'!$A:$A,1,FALSE)

Then copy the formula down as far as your column of data runs.

Where the result of the formula is FALSE, that data is not in the other worksheet.

Other Answer1

It might seem like a hack, but I personally prefer copying the cells as text (or exporting as a CSV) into Winmerge or any other diff tool. Assuming the two sheets contain mostly identical data, Winmerge will show the differences immediately.

comments:

Wouldn't this be more work than writing a simple formula where the data is already contained?
Nick: There's always more than one way to crack a nut, but there's no right or wrong way to do it so long as you use the tools you're comfortable with.
I didn't say you were wrong... It just seems a bit complicated to me when Excel has the tools already built in. Once I get a result in the external tool, I'm probably going to need to bring it back to Excel to work with anyway.

Other Answer2

LibreOffice provides a Workbook Compare feature: Edit -> Compare Document

comments:

Additionally, this feature also lets you Accept or Reject Changes into the document...so merging is a piece of cake!

Other Answer3

COUNTIF works well for quick difference-checking. And it's easier to remember and simpler to work with than VLOOKUP.

=COUNTIF([Book1]Sheet1!$A:$A, A1) 

will give you a column showing 1 if there's match and zero if there's no match (with the bonus of showing >1 for duplicates within the list itself).

comments:

This process will work, and will be less work for the user. In terms of computing power, a VLOOKUP is more efficient unless you are checking for duplicate values as well.

Other Answer4

I think your best option is a freeware app called Compare IT! .... absolutely brilliant utility and dead easy to use. http://www.grigsoft.com/wincmp3.htm

comments:

That is a great tool. Thanks!
I am not sure if the web site working any more? Some of the images are broken and I was unable to download the app.

Other Answer5

May be this replay is too late. But hope will help some one looking for a solution

What i did was, I saved both excel file as CSV file and did compare with Windiff.

comments:

Copying and pasting into Winmerge as Juliet suggested is easier and faster

Other Answer6

Use the vlookup function.

Put both sets of data in the same excel file, on different sheets. Then, in the column next to the 805 row set (which I assume is on sheet2), enter

=if(isna(vlookup(A1, Sheet1!$A$1:$A$800, 1, false)), 0, 1)

The column will contain 0 for values that are not found in the other sheet, and 1 for values that are. You can sort the sheet to find all the missing values.

Other Answer7

Use conditional formatting to highlight the differences in excel.

Here's an example.

comments:

The problem here is that every single cell after the first occurrence of a difference will then be highlighted. True, you could fix the first occurrence to find the next one. But you'll have to redrag the formula after each fix, and if you have a lot of differences, this is simply infeasible.

Other Answer8

I used Excel Compare. It is payware, but they do have a 15 day trial. It will report amended rows, added rows, and deleted rows. It will match based on the worksheet name (as an option):

http://www.formulasoft.com/excel-compare.html

Other Answer9

Easy way: Use a 3rd sheet to check.

Say you want to find differences between Sheet 1 and Sheet 2. Go to Sheet 3, cell A1, enter =IF(Sheet2!A1<>Sheet1!A1,"difference",""). Then select all cells of sheet 3, fill down, fill right. The cells that are different between Sheet 1 and Sheet 2 will now say "difference" in Sheet 3.

You could adjust the formula to show the actual values that were different.

Other Answer10

excel overlay will put both spreadsheets on top of each other (overlay them) and highlight the differences.

http://download.cnet.com/Excel-Overlay/3000-2077%5F4-10963782.html?tag=mncol

Other Answer11

ExcelDiff exports a HTML report in a Divided (Side-by-side) or Merged (Overlay) view highlighting the differences as well as the row and column.

Other Answer12

Excel has this built in if you have an excel version with the Inquire add-in.

This link from office webpage describes the process of enabling the add-in, if it isn't activated, and how to compare two compare two workbooks - among other things.

The comparison shows both structural differances as well as editorial and a lot of other changes if http://office.microsoft.com/en-us/excel-help/what-you-can-do-with-spreadsheet-inquire-HA102835926.aspx