Compare two Excel sheets

Question

I have an Excel file which contains two sheets. Both contains some data. I have to find common records of the two sheets(i.e., records which are present in both sheets). I can do it manually, but is there any tools/functions which exist to do this?

Answer 1

You might be able to use VLOOKUP depending on how complex your key structure is.

Answer 2

You can covert it .csv and there are lots of options

If you have access to Unix you can run diff <file1> <file2> or sdiff <file1> <file2>

If you have access to TortoiseSVN or TortoiseGit or Eclipse, you can diff those two files and the differences will be done on screen. For Tortoise select the two files and click Diff. For eclipse drag those two files into the gui, select and choose diff

If the files are not very big try - http://neil.fraser.name/software/diff_match_patch/svn/trunk/demos/demo_diff.html You can put your text into the two boxes and run a diff using a proven diff algorithm [Myer's diff]

If you are Windows you can use WinDiff or WinMerge - http://winmerge.org/ . I personally use WinMerge as it can diff directories.

Hope this helps.

Answer 3

This tool CompareIt does a good job at visually showing differences. It knows Excel natively.

Answer 4

There are several formulas you can use in Excel depending on what you want to do. The formulas all require a single-column key on both tables that they use to match rows. If you don't have a single-column key, then you need to create one. For example, if your data is in B2 to H500 and the key is the first three columns, create your single-column key in cell A2 with =B2&"-"&C2"-"&D2 and copy it down to the other rows. Do this in both tables and now you have a key column to use.

The main two formulas I would use in Excel are =COUNTIF(range, criteria) and =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). COUNTIF counts how many times criteria in table1 is found in range table2. If the result is greater than zero, then the row is in both tables. VLOOKUP looks up the lookup_value table1 in table_array table2 and returns the value in column col_index_num. If VLOOKUP doesn't find a match, it returns NA().

If you want to see info from table 2 in table 1, use VLOOKUP. If you just want to know if a row is in both tables, use COUNTIF.

If you don't want to add anything/or change the existing Excel tables, you can link to them in Access and run a basic query there. (Let me know if you want details on how to do this.)

Answer 5

It's not free and it might be overkill for your needs, but Beyond Compare has done a great job of comparing Excel sheets for me.

Answer 6

There is a specialized tool. Excel Diff seems to output result visually. http://www.suntrap-systems.com/ExcelDiff/

Try to consider because there is a trial version.

HTH