Macro to consolidate the data from mutliple files using Vlookup / Match

Hello,

I am looking for a Macro to copy the data from multiple excel files to single file.

Here it goes:

I have mutiple excel files in one location named as DownTime3_6_2012_1_23 (File saved at 1:23 on 3/6/2012), DownTime3_5_2012_14_21, and so on.......... Now I want the Macro to create a consolidated report which will already have the server list updated in Column A of result file (ConsolidatedResult.xls) and it need to do the Vlookup/Match with the files and copy the data as per the attached sample file (ConsolidatedReport.xls).

Any help would be appreciated.

Regards,
Pradeep

Try it. All files must be placed in one folder.(IFERROR works in Excel2007(10))

Please Login or Register  to view this content.

You can change this line if you use the date format mm:dd:yyyy

Please Login or Register  to view this content.

Thanks for the code Nilem, Its copying the data in Consolidated sheet but I am getting the same date twice one with correct data and other with previous date data. I have attached the result sheet. Please have a check.

P.S: I will have around 30 + files in same folder and the Macro need to pull the information of all the files. Also it’s not taking the date from the name of the file (I have changed 3_5_2012 to 3_21_2012 but it is still showing the same date as 5/3/3012, 6/3/2012 & 6/3/2012 in Consolidated).

I have the files in my desktop and the formula looks like below

for 3/5/2012

=IFERROR(VLOOKUP($A2,'C:\Documents and Settings\Administrator\Desktop\New Folder\[DownTime3_5_2012_14_21.xls]Sheet1'!$A$1:$B$65536,2,0)/60,"Not Pingable")

for 3/6/2012

=IFERROR(VLOOKUP($A2,'C:\Documents and Settings\Administrator\Desktop\New Folder\[DownTime3_6_2012_1_23.xls]Sheet1'!$A$1:$B$65536,2,0)/60,"Not Pingable")

For 3/7/2012
=IFERROR(VLOOKUP($A2,'C:\Documents and Settings\Administrator\Desktop\New Folder\[DownTime3_7_2012_4_23.xls]Sheet1'!$A$1:$B$65536,2,0)/60,"Not Pingable")

and so on...

what does C1:C2 does in the below formula?

Please Login or Register  to view this content.

Regards,
Pradeep