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

Ajoy

New member
Joined
Feb 4, 2013
Messages
12
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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

New member
Joined
Feb 4, 2013
Messages
12
Reaction score
0
Points
0
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

New member
Joined
Feb 4, 2013
Messages
12
Reaction score
0
Points
0
Hi NBVC,

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

Thanks

AJ
 

Ajoy

New member
Joined
Feb 4, 2013
Messages
12
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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.
 
Top