A Case Study: Excel report generation combining data from multiple Excel workbooks

I was approached recently by a client to see if there was any opportunity to help them streamline how they generate reports for some of their clients.  They had a situation where they had numerous excel spreadsheets that needed information consolidated down into one spreadsheet and vlookups just couldn't cut it.  These numerous spreadsheet reports were similar but they were cumbersome to create as it required lots of manual copy and paste, transforming of data, looking in other spreadsheets, searching for codes, copying information and pasting in the other spreadsheet.  All told, they had a frustrating process that took too many hours or days to create these worksheets and often times there were errors due to humans doing the work. 

This screamed out to me that this is the perfect opportunity to improve how they work.  Why?  Well, at its core was a repetitive process that took way too long to do manually.  As long as there was a consistent process and logic that could be written in code and the client agreed to adhere to a few rules and consistencies in the spreadsheet, this would be a huge time-saver for them.

So what I did was create a macro in the primary Excel workbook that did the following:
1.  Create a new worksheet to the create the report
2.  Reads a specific worksheet they do their daily work from and grabs some key pieces of data to insert into the worksheet created in step 1.
3.  Prompts the user to select the other workbook to reference to pull data into the worksheet created in step 1 (think of this as "lookup tables" to fill in additional information based on a code).
4.  Reads the workbook selected in step 3 to fill in additional data into the worksheet created in step 1.
5.  Format the worksheet created in Step 1.

There was obviously some more detail within each of these steps, especially steps 2 and 4.  However, the key was there was repeatable logic that could be created within these steps.  For example, while putting together step 2, in one instance we had to check to see whether or not a code already existed, and if it did, don't write it again. 

The client needed to agree to a few conditions in order for this to work:
1.  The source worksheets they update continually need to have the header rows be the same all the time and some columns need to have the same text in the header cell and possibly be in the same location each time.
That is it - not much to give up really.  In fact it ensures they do things consistently across their group.
2.  The source worksheets data needed to adhere to standards.  As long as the expected data was in the worksheet and the logic to read the data was repeatable, then all systems were go.

Some may think they can just record a macro, going through the process of doing it once manually, without the need to have someone with skills to write VBA code.  However, there are a few fatal flaws in this line of thinking:
1.  The source data may have any number of columns and also any number of rows of data.  Recording the macro does not take this into account and will result, at some point, into data being left out.
2.  When referring to another spreadsheet to pull data in, you must refer to the same workbook every time with recording a macro.  By using code, a custom form can be created that allows the user to select the workbook they want to reference to pull data in, allowing for greater flexibility.

At the end of the day they got a tool that:
1.  Was a huge time saver, freeing them up to do other things.
2.  Created numerous reports within seconds
3.  Was setup in such a way that previous reports would not be overwritten, creating a history if needed.
4.  Allowed users to have multiple copies of the workbooks, and even the workbook they needed to reference to pull data in, to account for different workstreams, different customers, and allow for historical archiving of workbooks.

If you have a similar problem with Microsoft Excel, contact us at Tucamino Solutions to see how we might be able to help you.  This type of solution also works well with querying data from a database, or even multiple databases, to pull data into Excel and format in a consistent manner.  You will be surprised how affordable these solutions can be and how you can get great ROI on this as it frees up your time to do other stuff than formatting and pulling data together in Excel.