Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
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
    
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
add comment

7 Answers 7

up vote 2 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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