Our client came to us with a brief to produce a Work Order Control spreadsheet. A customer would send electrical engineering work orders to our client on a daily basis. The client would then assign these orders to a company meter engineer. This was achieved by searching the work schedule to place an available engineer against their customer’s work order list. Then a job would be assigned to the engineer. This was a painstaking manual exercise that took a couple of hours each day to complete. The client wanted an automated solution to the process that saved time and allowed further data to be added.
One of Excel’s key attributes is its ability to automate repetitive manual tasks. The coding language that sits behind Excel is called Visual Basic for Applications (VBA). A developer will write small sections of this code, called macros, to undertake specific tasks. One of the most effective uses of VBA is programming Excel to repeat tasks over and over again, extremely quickly.
The Work Order Control spreadsheet first permits the client to browse to where the source files are saved on their computer. Then the client runs a macro that replicates the manual task of matching work orders to company engineers.
Work Order Control spreadsheet – front end
The user is first presented with a front end spreadsheet (see below):
The user clicks each ‘Browse’ button in turn to navigate to where they saved the two Excel files. In the image above, these are ’2017_SCHEDULE.xlsx’ and ‘work_order_list_schedule.xls’. The location of each file is then stored in the boxes to the left of the ‘Browse’ buttons. Then the user clicks the green ‘Start’ button to run the procedure. The program then runs the VBA code in the background. The entire procedure typically takes between 30 and 60 seconds to execute.
Work Order Control spreadsheet – the procedure
Before they came to us, our client would manually search each work order line by line to match each job to an engineer. Our procedure enables Excel to do this automatically, and more besides. The program stores each work order, according to its month and year, in the relevant Excel tab in the work schedule spreadsheet (see below):
The procedure also searches each work order, line by line, and colour-codes the line. This is based upon whether the order has already been assigned (green), whether it is a new order (blue), or if the order is not found in the work schedule (red; see below):
As each work order row is updated or added to the work schedule, additional data for each order is added from the work order list to its row in the schedule. For our client, the procedure added the work order reference number, the MPAN (Meter Point Administration Number) and the postcode of the meter location.
Work Order Control spreadsheet – results summary
Upon completion, the ‘2017_SCHEDULE.xlsx’ spreadsheet appears on screen. A summary of the number of work orders matched, added or not found is displayed in a message box (see below):
The work schedule is now updated. The client is then able to manually add further detail to the work order rows in additional columns (see below):
How can ExcelFrome help you?
This Work Order Control spreadsheet was written for an electrical meter engineer company. However, we at ExcelFrome can tailor a similarly effective job logging solution for your company, whatever the industry sector. Find out how we can help your business save valuable time and resources with the power of Excel. Please leave your details in the boxes at the foot of this page or contact us via email or phone.
