Comparing and marking data from two different workbooks

[Aralei]

Hi Everyone,

I'm quite new with excel programming and I would like to get your help regarding one of my projects.

I have an excel report that I need to pull up three times a day. The report contains server names and the status of an agent installed on that server. The status varies from running, unknown and maintenance mode. The number of servers listed on the report changes in a indefinite amount of time as well as the agent status of those servers.

My project involves comparing one report generated on an earlier time (e.g AgentReport1), and one report pulled up on the current time (e.g AgentReport2). Aside from that, what I need will be a brand new report with the combined list of servers along with all the other information specified on the other colums from both workbooks EXCEPT the servers that are in AgentReport1 but no longer in AgentReport2.

I tried using some codes i found over the net but didn't work for me.

I hope you guys can help me out with my project. I have attached a sample report for reference.

Thank you very much in advance for helping me out.

[arlu1201]

Please attach AgentReport2 as well.

[Aralei]

Here's a sample copy of AgentReport2.

By the way, I just realized, i might have explained my required output in a wrong way. What I actually mean is that the servers in both workbooks will be combined in one new workbook but for servers both in AgentReport1 and AgentReport 2, the information on the other columns will be filled with the information from AgentReport2. Aside from that, servers in AgentReport1 but not in AgentReport2 will not be copied in the new workbook and servers in AgentReport2 not in AgentReport1 will also be carried over to the new workbook.

Thanks for helping me out. I really apprreciate it.

[Aralei]

bumping thread!

[arlu1201]

Aralei,

Try this code on a dummy file and let me know if it works for you.

You need to copy this code into a standard module (see how below the code) of a blank file. It will prompt you to open AgentReport1 & AgentReport2. Just change the file paths in the highlighted part of the code below.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[Aralei]

Hi Arlette,

Apologies for the very late response. The code works! Thank you very much!

I just have one last question though. I'm actually planning to have the AgentReport1 and AgentReport2 placed on a certain folder on a different server. When I try to use the code using the path from the separate server, the code doesn't work anymore. Do you know what should be the format of the path file in the code to make it work?

Again, thank you very much for your help!

[arlu1201]

You have to just replace the bold section in the code to your path and it should work.

If its slowing down the macro because its on a server, you can copy the files to your local drive and change the path in the macro and then run it.

[Aralei]

Hi Arlette,

Apologies for the very late reply. If you take a look at AgentReport1, there some server which has an unknown agent state but has a comment beside it marked as troubleshooted. For the scenario wherein a server is both on AgentReport1 and AgentReport2, it it possible to have the information to be posted in the new document to contain all the information from AgentReport2 but would also include the Previous Action column for AgentReport1?

For example, the Server16 mentioned in both reports, the new report would also include the "Troubleshooted" note from AgentReport1 beside the information taken from AgentReport2.

I'm really in debt of you helping me out with this one. Thank you very much!

[Aralei]

Bumping! Thanks!

[arlu1201]

So do you need an additional column to show the previous action column from report1?

[Aralei]

Yes. To sum it up:

- If the server is in both reports, server information to be posted will be from AgentReport2 from columns A to H but with the information from AgentReport1 at column I

Regards,
Aralei

[arlu1201]

Try this code and let me know if it works -

Please Login or Register  to view this content.

[Aralei]

Hi Arlette,

It works! Thank you very much for your assistance!

I'll be closing the thread now. Thanks again!