Need to compare two different excel workbook and difference should be highlighted

[shanvin]

Hi,

I am new to macro ,I have two differnt excel workbook contains huge data more than 4lakhs records.

Please suggest me a macro which can be used to compare both the workbooks

It will be good if u provide me step by step process


Regards
Shanvin

[arlu1201]

What do you want to do after comparing?

Fill in missing records or highlight discrepancies?

Its better you upload a sample of both your files -

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[shanvin]

Hi,

Thanks for your reply,

Actually what is happening in my case is
I am generating the excel report in the legacy system with an input say A and for the same input A iam generating the report in newly buit system


So I want to make sure that both the excel have same data without any mismatch after the migration.

I was using the below macro to compare the excel files -will take the data o

Please Login or Register  to view this content.

What is used to do is I copy the data of old system into sheet 1 ofnew excel file and copy the excel of newly built application into
sheet 2 of excel file and run the above macro so it will display me the matched values in greencolor and unmatched value in red color

As i said in earlier post data may be huge in the excel file so manual comparison is not possible and also iam unable to copy the huge data so it will be good if we have a automation process


Regards
Shanvin

[arlu1201]

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

[arlu1201]

Were you facing any issue when you ran the code you already have?

Also, why were you not able to copy the data? You have said

also iam unable to copy the huge data so it will be good if we have a automation process

[shanvin]

As i replied in the below post If i copy the huge data and run the below macro iam gettinga an errror error -9 .Subscript out of range

[arlu1201]

A subscript out of range error occurs when the sheet you are referring to is missing or is not spelt correctly. Check your sheet names in the file with the code sheet names and let me know if it works.

[arlu1201]

I would suggest you do it this way -

Consolidate the data from excel workbook 1 and excel workbook 2 to the same sheet in excel workbook 3.
Ensure that you keep a column to designate which file the data came from.
Then sort the data based on a unique field.
Compare row by row - you will be comparing one row from workbook 1 and the other row from workbook 2.

Let me know if you need help with this.

[shanvin]

Hi,

I need a help in writing macro for the below logic,

a.)I have to choose first excel file from the directory and selected file should be displayed.
b.)After Selecting I have to copy the data from selected excel sheet to a new excel sheet let say into sheet1 of a new workbook.
c.)Choose the second file and copy the data to sheet2 of a new workbook.
d.)Then I have to compare the data of sheet1 and sheet2.

I am attaching the macro sheet which i have used for your refernce

where in the attached macro ....if there in an empty cell macro is not copying the complete data...Please advise