Compare two workbook

Please attach a sample workbook so we understand your requirement.

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.

After comparing, what output do you need?

Ok, i understood you .

One more question - will world1 in workbook1 be in the same row as hello_world1 in workbook2? Will the entries be in the same row so they can be matched or they can be anywhere in the column?

Try this code

Please Login or Register  to view this content.

For this example, i have saved the files as workbook1.xlsx and workbook2.xlsm. The macro needs to be saved in the 2nd workbook. The macro assumes that workbook1 is also open at the time of running the macro.

Copy the Excel VBA code
Select the 2nd workbook in which you need to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

Let me explain to you what it does:
1. Since the format of both the files is not same, a column is inserted in column A of workbook2. Using formulae such as len, right, find, only the world1, world2, etc part of the text is inserted into column A.

2. Another column is inserted in column B. Here the number 2 is inserted to show that the data has come from workbook2.

3. The data from workbook1 is copied in column A below the existing data. In column B, the number 1 is inserted for each row belonging to workbook1.

4. The data is sorted based on column A.

5. A condition is applied to the data - If row 1 and row 2 match, then the word "Same" is entered in column D, if not, the word "New" is entered.

Hope this works for you. Please try it on a copy of your file.

What are the values you have in columns A & B? Are they the same as what you posted in this thread?

Yes, it should work with 2 excel workbooks. What do you have in columns B, C & D? There is some data in there that is causing the macro not to work. See if you can attach a sample file, or else it will be tough to troubleshoot the problem.

The macro inserts columns in A & B. So ideally your data in B-D will now move forward to D-F. So the macro should not be posing any problems.

If its still posing problems, i can have the comparison done in a temp worksheet. What do you suggest?

Try this code which creates a "Temp" sheet and does the calculations.

Please Login or Register  to view this content.