We had an employee that was responsible for downloading a report that provided information on documents that had not been closed. These are documents that are either still being worked, have been suspended, or have been looked over or missed. Her job was to take this report and sort all the information by work group. She also had to sort the information by how long the documentation was overdue. Once she accomplished this task, she had to submit a report showing her findings. This process took her half a day to complete.
I was asked if I could automate this process. The solution was creating an excel template. I created a workbook with a worksheet for every group; 24 in total. I then created a blank worksheet for the original report to copy into. Lastly I created a cover page that would house all the calculations.
First the Macro would import the original report into the template. This was easy since the online system always named the report the same thing. Next, the Macro would separate all the information into the blank section tabs. Once everything had been sorted by work group, the Macro will ask the employee to enter the date. This will be the date all the calculations will be based from. Afterwards, just for fun, an audio file is played that says “Great job. Feel free to take the rest of the day off.”
Using a countif formula, I pulled the length of time each document was late from every worksheet. I calculated the totals on the bottom of the page, and also included percentages of those totals. This workbook could then be saved and sent via email.
A process that originally took half a day to complete was now only a 5 minute job.