Project Deliverables Optimization Using Microsoft Excel
Project Background
While working HDR as Transmission and Distribution EIT, a majority of our deliverables were created used Microsoft Excel. This was because we dealt with Overhead and Underground design so it was easiest to use Microsoft Excel and produce professional documents. When I was first on boarded to the team the excel sheets where very simple and required a lot of manual copying and pasting units between different sheet. This allowed for easy mistakes to be made both in formatting as well as units being counted or edited properly because there was potential for edits to be copied into one area not another and not be counted in the BOM. With some in experience in Excel from a class I took in my undergrad I took some time outside of work to renovate the excel sheets to alleviate some of the pains required when finalizing Staking Sheets and the BOM
Method of Optimizing
I decided that the best way to fix the issue of having to manually copy and paste units over to individual sheets was to utilize macros and write a script to do it. I would write segment of code in VBA then implement buttons to do the commands. Each command would have its own button to reduce run time and allow for the user to redo particular parts without having sit through the entire script rerunning. For this project to be successful the commands had to be simply and follow the current work flow for how delieverables were made but speeding up the process with automation.
Operations need to be Automated:
Copying units from the All Combined sheet to individual Staking Sheets
Copy the units from the All Combined sheet and create a Property Units page (Bill of Materials with out Costs)
Copy the units from the All Combined sheet and create a Property Costs page (Bill of Materials with all associated Costs)
Select all individual Staking Sheets and print in the proper format
Adding Features and Functionality
When I finally got the original features settled with all the bugs out I showed my boss and teammate what I had made. Being impressed he allowed me to spend some non billable time to continue working on the Excel sheet and add more features. Over the next several weeks I added several features that would assist with catching small mistakes and fix some counting errors our previous sheets had.
Enhanced Features Added:
Units Check: implemented a series of conditional formatting to ensure all units in the staking sheets were compatible with one another and were not missing units that are intended to be used together
Data Validation: Set cells to only allow a list of allowable units for each column to reduce number of typos and units not being counted from the misspelling
Automatic Formatting: Reset the format to ensure the split lines were in intended spots to ensure a proper copying of units from the All Combined sheet to individual Staking Sheets
Create Property Units and Costs: Would create the Property Units and Costs sheets from scratch to ensure common formatting and reducing time needed to add spacing
Printing Property Units and Costs: Sets the printing boundaries for the Property Units and Property Costs to ensure the they are not being split on to separate sheets
Create Seperate Properties Cost Excel Book: Would create an entirely new Excel Workbook and copy the Property Costs over while leaving pasting only the values for the costs and keeping the formulas for the sum of the costs. This would be sent to our third party reviewer to ensure everything was being counted correctly while not sharing client pricing specifics
Creating Separate Excel Book for Variant of Bill of Materials: Would create a separate Excel Workbook and create a variant of the Property Units that would be sent to our client for ordering materials, this required some different information and a different format than our property costs and was done manually before.
Creating Second Version of Code for Underground Staking Sheets
The Overhead sheets were so productive I was asked to replicate this process for our Underground Staking Sheets. The main challenge with this request is that the OH staking sheets had a set number of columns that would never increase. The UG staking sheets had a different format where columns would be added for additional units while also adding rows for addition station where equipment would be installed. It would require an entirely new method of transferring the information to individual staking sheets as well as creating a bill of materials