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?
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
Eclipse, you can diff those two files and the differences will be done on screen. For
Tortoise select the two files and click
eclipse drag those two files into the gui, select and choose
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.
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
VLOOKUP doesn't find a match, it returns
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
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.)
This tool CompareIt does a good job at visually showing differences. It knows Excel natively.
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.
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).
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)