• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparing two Excel Files to populate one column in the second file

A

AJExcelStar

New Member
There are two separate reports as two separate Excel file. The old one is on a server and the new one is pulled once each week from a system where data is updated all the time. Column G is added to the new report as a Insert Column function.


However, 100s of new rows are added each week. So the old report will not be having those new rows actually.


The logic necessary to populate G column on the new report is - "If the respective cells for column A, E, F and H in last report and new report ARE THE VERY SAME, what ever was there in the old report in the Column G in that row should get populated in the new report Column G. Otherwise it should be populated with blank."


This means all newly added rows should be populated with blank in the G Column of the new report.


I am looking for some Macro or Merge or any other type of Formula that can be used in this case but VLOOKUP will not work in this case. Any suggestions or guidance would be highly appreciated. I also must say that it is impossible to do this manually as there are more than 6000 rows in the reports.
 
Hi AJExcelStar,


It would be my pleasure to help you on this.


May I request you to upload a sample file in order to help us write the code and test it on the data?


Please see the below link for the same.


http://chandoo.org/forums/topic/posting-a-sample-workbook


Kaushik
 
Hi Kaushik,


Thank you very much for your help. I have uploaded the samples with names Old Report and New Report in skydrive.live.com


The link is https://skydrive.live.com/?cid=15E94FDF4E576944#cid=15E94FDF4E576944


In case you cannot access this, I will need your e-mail to send you the shared link.


Thanks again for your help.


Best regards


AJ
 
Hello AJ,


I am not able to download your file my friend. It is showing folder is empty.


Could you upload it in www.speedyshare.com/ ? It's free and no registration is required.


However, below here is my e-mail ID.


pharmacyjukaushik[at]yahoo[dot]com


Kaushik
 
Hi Kaushik,


Thank you very much for your prompt response and your help. Perhaps you could not access the files because without your e-mail skydrive does not give access to the files. But now I have sent the shared links to your e-mail pharmacyjukaushik[at]yahoo[dot]com, I am sure you will be access the files now.


In case that does not work, I have also uploaded the files on speedyshare.com and the links are below.


http://speedy.sh/jAzgM/New-Report.xls

http://speedy.sh/g9xUE/Old-Report.xls


Thank you very much for your help.


AJ
 
Hi Kaushik,


Please let me know if you could get the files. I have tried with IFMATCH and it did work, but after certain rows it is giving a crazy date as output 1/0/1900


I am using =IFERROR(INDEX(Old!G2:G2,MATCH(A2&"^^"&E2&"^^"&F2&"^^"&H2,Old!AG2:AG2,0)),"")


Thank you for your help!


AJ
 
You must log in or register to reply here.
Back
Top