Where have my vans been?

Step 1.png

Where to start?

Starting with our fleet management software I am able to answer this question. But it is clunky and not the easiest thing to read, nor assess. Since I know I will be moving on from this company, my goal was to be able to answer this question easily.

Step 2.png

Time to Excel

Exporting the raw data from the fleet management software is pretty straight forward. The primary users are adept with computers so I can rely on them to be able to update this excel sheet and use it properly. As long as this info is pasted into the correct table, the formulas will automatically paste as needed.. First formulas are simple, which involves breaking up cell contents to extract the date and time.

Step 3.png

There is a better way...

First solution after getting the data set up correctly was to use filters to drill down to the information I want. While it technically worked and was able to answer the original question, it was time intensive to answer this question weekly for different vans. I wanted to make this process easier for my replacement.

Step 4.png

One step closer to automation

With some googling and tinkering, I used an array with index and multiple criteria. First step to get the number of expected responses, then to set up this formula: =IF(ROWS($1:1)>$I$45,"",INDEX(Table2,SMALL(IF(Table2[Asset]="Van 1",IF(Table2[Real Start Date]=$A$44,IF(Table2[Same Day?]="Yes",ROW(Table2)-1))),ROWS($1:1)),5))

This will pull a cell with an unique identifier. From here I can use simple index match to pull the information I want.

The end result is, to answer this question, all that is required is to paste the data from the fleet management software and then change the date. From there the formulas take over and pulls all of the correct info.