At Good Dog People, they would receive 80-100+ orders a day. These orders had to be segmented into different areas of Singapore. Then each respective area would be assigned to a particular driver or a set of drivers. This task would take a bulk of the staff's time. As the company orders grew, so did the time take to complete this task. Eventually, on Mondays, the employee in charge would usually have to work a little past 6 pm.
Since most of the steps were using Excel, I picked up Excel VBA to attempt to automate the process. First I figured out the way the company segments Singapore. The following image was the visual representation. The red text and base image shows the district segmentation (first two digits of the postal code) in Singapore from an official site. The grey overlay showcases how the company separates the segments.
With this information, I attempted to fine-tune the district numbers by individually checking on google maps. The following data was what I finalised, before beginning to work on the automation.
| East 1 | East 2 | Central | Central West | North East 1 | North East 2 |
|---|---|---|---|---|---|
| 38-47 | 48-52 | O1-O8 | 1O-16 | 28-29 | 82 |
| 81 | 17-27 | O9 | 31-37 | 53-54 | |
| 30 | 59 | 55-57 | 79-80 | ||
| 58 | |||||
| North | West 1 | West 2 | |||
| 72-73 | 60-64 | 65-68 | |||
| 75-78 | 69-71 |
Using VBA Excel, I created multiple macros for different actions. Then to create a button, I linked them all to one macro and by running that, all the other steps would be done in sequence. The user would have to download the order data from Shopify, open it in excel and then run the master macro. The list of macros can be found below in the report.
I also created a user manual so all the machines could possess the automation on their own excel.
Using this automation did solve the problem. However, on days when there were more than usual orders (i.e Monday). Sometimes, a few times, the script would crash halfway. This did not happen often, however, it was a cause for concern. If the company continued to grow this would pose a serious issue.