3

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?

  • 2
    Minimally, you could convert to a csv and then run grep or something similar. You are looking for records that are identical in all ways? what do you want to do with them – soandos Apr 1 '12 at 7:02
  • 1
    I'd convert to CSV and then write a simple Java program or whatnot to extract the relevant sections for comparison with your favorite diff tool. Or have the program compare them directly. – Daniel R Hicks Apr 1 '12 at 13:29
2

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

(Do a search on the Microsoft Office Support website for VLOOKUP.)

|improve this answer|||||
  • Link is unfortunately dead. – Richard Aug 21 '18 at 11:30
  • 1
    Grumble grumble. I guess when you're Microsoft, you don't have to worry about creating 301 redirects when you restructure a site. – Scott C Wilson Aug 21 '18 at 12:27
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.

|improve this answer|||||
  • diff/sdiff are great tools for when you have two files that are largely the same, with a few lines different. They are not so useful for files that are largely different with a few lines the same. – Scott Jan 10 '19 at 23:53
2

If the cells would be in the exact same location in each sheet, you can use a third sheet, then fill down and right with this formula:

    =IF('Sheet1'!A1<>'Sheet2'!A1,"Different", "-")

to compare the changes, where "Sheet1" and "Sheet2" are your sheet names.

|improve this answer|||||
1

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

|improve this answer|||||
0

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

|improve this answer|||||
0

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.

|improve this answer|||||
0

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

|improve this answer|||||
0

Just my two cents, as nobody mentioned it here so far you could try also Excel Compare by Formula Software (it's paid software but comes with a free trial period).

enter image description here


Anyway the best solution, in my opinion, is maybe already stated on your question's comments: export to CSV and then use any diff-tool you like to compare, simple! (a list of diff-tools here)

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.