Compare Excel Column in two excel files?
-
Hi, I am maintainng Daily report for my company, I have Two columns in Excel file called A, Col. 1 is the voucher nos i.e. 123412 and the Col. 2 contain amounts i.e. 50, then i have to compare with another Excel File called B, which also have the same columns i.e. voucher and amount. Is there any excel function or formula so that i can compare both Excel Files and the columns to check the voucher nos. and amount in File A are similar to the File B. And if any one do not match with the other file, it highlights.
-
Answer:
It's possible, and once you know the methods, pretty easy to do. The key function you're looking for is VLOOKUP. Look at the Excel help for it, or google around. VLOOPUP finds values in other places that match values in a starting place. What you'll do is add a third column in the first sheet that references the voucher number and pulls in the amount from the second sheet. Then, add a fourth column comparing the amounts from the first and second sheet. Do the same thing with sheet 2 as a base to make sure you've got everything covered. VLOOKUP can be a little confusing at first, but once you get it, it's one of the most powerful things in all of Excel. Have some patience, it will work.
The_Paki... at Yahoo! Answers Visit the source
Other answers
Let us assume that you have x records in each file. Now to compare the voucher numbers and amounts in file B with that of file A, you can create a new column in file B, where you can insert the formula in cell ref. C1. =vlookup(A1,[fileA]Sheet1!$A$1:$B$x,2)… Now drag and copy the formula to all the rows in column C. In cell D1, enter the formula =B1=+C1 Then drag and copy the formula to all rows in column D Wherever the fields are equal you will get the answer 'True', else you will get the answer 'False'.
Eechhutti
In the second file: New column: use a lookup function to find the voucher in the first file, New column: use an index function to find the amount in the first file New column: work out the difference. use conditional formatting to highlight any non-zero difference Hope this helps, but I haven't got time to do it for you
Press C
Related Q & A:
- How do I compare two tables with the same column definitions in different schemas?Best solution by Database Administrators
- How to covert csv file to excel and back excel file to csv in python?Best solution by completecampaigns.com
- How can i attach word,excel,mp3,picture files to e-mail messages in yahoo mail quickly?Best solution by Yahoo! Answers
- How to make Outlook 2007 as default for emails while sending files directly from Word or Excel as attachments?Best solution by Super User
- How to share files between two computers?Best solution by Ask Ubuntu
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.