Comparing two Excel files to populate one column in the second one

[Ajoy]

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.

[NBVC]

In the old report, add a helper column that concatenates, A,E,F and H.... e.g =A2&"^^"&E2&"^^"&F2&"^^"&H2 copied down. Note, the ^^ in between is there to avoid false positives when comparing.

Now in new report you can use INDEX/MATCH

e.g. =IFERROR(INDEX('[OldReport.xlsx]Sheet1'!G:G,MATCH(A2&"^^"&E2&"^^"&F2&"^^"&H2,'[OldReport.xlsx]Sheet1'!I:I,0)),"") copied down

where I:I is the column you used as a helper in the old report. Change workbook name, sheetname name and references to suit.

[Ajoy]

Thank you very much for your suggestion, NBVC! I will surely try it out and get back to you how it worked.

Best regards

AJ

[Ajoy]

Hi NBVC,

The =A2&"^^"&E2&"^^"&F2&"^^"&H2 is causing a circular reference error. Can you suggest how to resove this?

Thanks

AJ

[Ajoy]

Hi NBVC,
I have resolved the circular reference error, it was my mistake, sorry about that. Thank you very much for your help. The formula is not working for the New Report, I have been trying hard to resolve this but till now no positive results. In my case the formula becomes

=IFERROR(INDEX([D:\data\542328513\Desktop\(Old Report.xlsx)]Sheet1!G2:G2,MATCH(A2&"^^"&E2&"^^"&F2&"^^"&H2,[D:\data\542328513\Desktop\(Old Report.xlsx)]Sheet1!AG2:AG2,0)),"")

This gives an error that file name is wrong. Can you suggest what may be going wrong? Thank you for your help.

AJ

[NBVC]

I would suggest you open the source file, then instead of typing in the path and range, select this part of the formula, then actually select the range in the other book.. it should autopopulate into the formula. Do the same for the [D:\data\542328513\Desktop\(Old Report.xlsx)]Sheet1!AG2:AG2 part.

After you enter the formula, then you should be able to close the source book to reveal the whole path in the formula.