Comparing the columns of two Excel files and populating the rest of the information

Hi All,

I am a newbie in Excel VBA programming. I have a simple question. I have two excel files. One having complete detail and the other having three columns which are similar to the first but are in a random order.

Now i have to compare the each row of all three columns with the other and populate the rest of the information by copying from one file to another.

This task seems to be simple. Could anyone please guide me in doing this? or please provide the code of how to do this?


If anyone can send any example likethis?


I have also attached the two files on which i am working. Now the first three columns of "template_new" file and "pdo1" file are same (ADDR,TAGNAME and TERM). What i want to do is to compare the first three columns and then to copy the rest of the columns from "template_new" file to pdo1 file. The "template_new" file has the VBA script behind it which is copying data from sheet 3 in it to sheet 1.


Could anyone please help me in doing the above task?

Regards,
mhs100

Can anyone please reply?

I did not get any reply yet. Can anyone please look into this?

Maybe:

Please Login or Register  to view this content.

Hi John,

Have you tried to run the above code in "template_new" Excel file? It is giving error on the first line.

Workbooks("pdo1.xls").Sheets("Sheet1").Columns(1).Insert

Please advise.

Thanks and Regards,
mhs100

If you're getting a Subscript out of range error, you need to rename your workbooks the same as your original files. When you download from the website copies it attaches another number (ie -1, -2). If there is another error then what is the error?

The names and directory are correct. I have also integrated your code correctly in the existing one. But still i am getting subscript out of range error. Attached are all three files. All the three files are in E:\macros folder. Please check.

One more thing do you have very good reference book for Excel Vba. So that i can walk through your logic.

Your help is appreciated.

Regards,
mhs100

I tested it again separately (without integrating the code into your previous code). It pulls the data from template to pdo1 (I think from Post 1 that's what you wanted?) and I can't duplicate the error?

Can you check the integrated version(above three files)? When i press the run macro button. The data from "IO_variable_report.csv" is copied to sheet 3 of "template_new" excel file. Then it is moved from sheet 3 to sheet 1 of "template_new" excel file. After that your code started, which should compare sheet1 of "template_new" excel file with Pdo1 and then copy the rest of the columns in pdo1.

Yes my code is working. I just put your code after mine.But i dont know what is causing the error?

Did you try running your code separately and then running mine?

yes my code is running fine, if i separately run it.

I also tried to run your code separately but it is giving the same error. Subscript out of range

Which line is highlighted when you debugg?

Workbooks("pdo1.xls").Sheets("Sheet1").Columns(1).Insert

Either the workbook name or the sheet name doesn't exist. Try .xlsx instead of .xls.

Hi John,

Now it is working but i dont want "0" as the return value. I want to be the cell remain blank. Can you modify the script for this?

Also, i am getting this line in the first line which should not be there.

=VLOOKUP(RC[-5],[template_new.xls]Sheet1!C1:C10,{6,7,8,9,10},FALSE)
Thanks,
mhs100

Maybe:

Please Login or Register  to view this content.

It is still giving the same result. Please check. Attached is the file.

I can't duplicate the zero's problem. Line 2 was a formatting issue and this should fix that.

Please Login or Register  to view this content.

Thanks. It works now.

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

Hi John,

Thanks a lot for your support. Sorry, i am a bit late in doing this activity. I tried to follow your instruction. But this thread has already been marked as solved now.

Thanks once again.