Fleet Fuel Tracking

[Lazyh0rse]

Hi, I'm a first timer out of dark Africa

I'm trying to track fuel usage for a small fleet.
My issue is how to calculate the distance traveled between dates on a specific vehicle.
Any help would be much appreciated.


[TABLE="class: grid, width: 427"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Vehicle ID[/TD]
[TD="align: center"]Date [/TD]
[TD="align: center"]ODO Meter[/TD]
[TD="align: center"]Distance km[/TD]
[TD="align: center"]Liters - Depot[/TD]
[TD="align: center"]Result[/TD]
[/TR]
[TR]
[TD="align: center"]Truck1[/TD]
[TD="align: center"]2018/10/01[/TD]
[TD="align: center"]14500[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]171[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck3[/TD]
[TD="align: center"]2018/10/01[/TD]
[TD="align: center"]32410[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]164[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck2[/TD]
[TD="align: center"]2018/10/02[/TD]
[TD="align: center"]20350[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]333[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck2[/TD]
[TD="align: center"]2018/10/13[/TD]
[TD="align: center"]20740[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]160[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck1[/TD]
[TD="align: center"]2018/10/15[/TD]
[TD="align: center"]14800[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]143[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck3[/TD]
[TD="align: center"]2018/10/19[/TD]
[TD="align: center"]32890[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]178[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck3[/TD]
[TD="align: center"]2018/10/19[/TD]
[TD="align: center"]33450[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]243[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck1[/TD]
[TD="align: center"]2018/10/21[/TD]
[TD="align: center"]15550[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]385[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck2[/TD]
[TD="align: center"]2018/10/22[/TD]
[TD="align: center"]21570[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]370[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck1[/TD]
[TD="align: center"]2018/10/25[/TD]
[TD="align: center"]15940[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]176[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck3[/TD]
[TD="align: center"]2018/10/26[/TD]
[TD="align: center"]33980[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]226[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
[TR]
[TD="align: center"]Truck2[/TD]
[TD="align: center"]2018/10/27[/TD]
[TD="align: center"]21980[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]185[/TD]
[TD="align: center"]0,00[/TD]
[/TR]
</tbody>[/TABLE]

 

[ranman256]

in a form , put 2 text boxes for the dates; txtStartDate, txtEndDate
a 3rd text box can hold the calc:

txtMiles = Dmax("[Miles]","table","[DAte]<=" & me.txtEndDate & "'") - Dmin("[Miles]","table","[DAte]>=" & me.txtStartDate & "'")

 

[Lazyh0rse]

Thanks for the reply, the only worst than asking for help is not understanding the answer.

Sorry I probably did not ask the question in the right way. Maybe it will help if I give a bit more info.

I have 13000 rows of info similar to the info in the attached example.
There are 25 trucks and the records go back to 2013.
Every time a truck fills up with diesel a new row is created.

So in my mind I need a formula in column D that refers to the vehicle ID, establish the previous ODO Meter reading for that vehicle and then subtract that from the ODO Meter reading that was just entered.

 

[Logit]

.
Paste in a module :

Option Explicit
Sub CreateSheets()


    Dim Cell    As Range
    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet


        Set RngBeg = Worksheets("Master").Range("A2")
        Set RngEnd = Worksheets("Master").Cells(Rows.Count, "A").End(xlUp)


        ' Exit if the list is empty.
        If RngEnd.Row < RngBeg.Row Then Exit Sub
Application.ScreenUpdating = False
        For Each Cell In Worksheets("Master").Range(RngBeg, RngEnd)
            On Error Resume Next
                ' No error means the worksheet exists.
                Set Wks = Worksheets(Cell.Value)


                ' Add a new worksheet and name it.
                If Err <> 0 Then
                    Set Wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                    Wks.Name = Cell.Value
                End If
            On Error GoTo 0
        Next Cell
Application.ScreenUpdating = True
MakeHeaders
End Sub


Sub MakeHeaders()
Dim srcSheet As String
Dim dst As Integer
srcSheet = "Master"
Application.ScreenUpdating = False
For dst = 1 To Sheets.Count
    If Sheets(dst).Name <> srcSheet Then
    Sheets(srcSheet).Rows("1:1").Copy
    Sheets(dst).Activate
    Sheets(dst).Range("A1").PasteSpecial xlPasteValues
    Sheets(dst).Range("A1:F1").Font.Bold = True
    Sheets(dst).Range("A1").Select
    End If
Next
Application.ScreenUpdating = True
CopyData
End Sub


Sub CopyData()
Application.ScreenUpdating = False
Dim i As Long
Dim lastRow As Long
On Error GoTo M
lastRow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
    For i = 2 To lastRow
    ans = Sheets("Master").Cells(i, 1).Value
        Sheets("Master").Rows(i).Copy Sheets(ans).Rows(Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1)
        Sheets(ans).Range("D3").Formula = "=SUM(C3-C2)"
        Sheets(ans).Columns("A:I").AutoFit
    Next
   


Sheets("Master").Activate
Sheets("Master").Range("A1").Select
Application.ScreenUpdating = True


dragformula


Exit Sub


M:
MsgBox "No such sheet as  " & ans & " exist"
Application.ScreenUpdating = True


End Sub


'paste this in Routine Module
Sub dragformula()
Dim lastRow As Long
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Application.ScreenUpdating = False




For Each ws In wb.Worksheets
    If ws.Name <> "Master" Then
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row


    ws.Range("D3:D" & lastRow).FillDown
       
    End If
Next ws
Application.ScreenUpdating = True


SortSheetsTabName


End Sub


Sub SortSheetsTabName()
    Application.ScreenUpdating = False
    Dim iSheets%, i%, j%
    Dim ws As Worksheet


    iSheets = Sheets.Count
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Master" Then
        For i = 1 To iSheets - 1
            For j = i + 1 To iSheets
            
                If Sheets(j).Name < Sheets(i).Name Then
                    Sheets(j).Move before:=Sheets(i)
                End If
           
            Next j
        Next i
        End If
    Next
    Sheets("Master").Activate
    Sheets("Master").Range("A1").Select
    Application.ScreenUpdating = True
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/fqsb8q0cQ4qPJpOIKkcPDcVYmkcwEAOAinC2IdMv4Ir

 

[Lazyh0rse]

Great thanks that's impressive, how do I update new data in this sheet?

 

[Logit]

.
Just continue adding data to the MASTER sheet.

Then click the button. It will do what you want automatically.

I made a slight edit to the code. Please change the existing macro ( CreateSheets ) for that shown below:

Option Explicit
Sub CreateSheets()


    Dim Cell    As Range
    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet


        Set RngBeg = Worksheets("Master").Range("A2")
        Set RngEnd = Worksheets("Master").Cells(Rows.Count, "A").End(xlUp)


        ' Exit if the list is empty.
        If RngEnd.Row < RngBeg.Row Then Exit Sub
Application.ScreenUpdating = False


        For Each ws In wb.Worksheets
            If ws.Name <> "Master" Then
                ws.UsedRange.ClearContents
            End If
        Next
        
        For Each Cell In Worksheets("Master").Range(RngBeg, RngEnd)
            On Error Resume Next
                ' No error means the worksheet exists.
                Set Wks = Worksheets(Cell.Value)


                ' Add a new worksheet and name it.
                If Err <> 0 Then
                    Set Wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                    Wks.Name = Cell.Value
                End If
            On Error GoTo 0
        Next Cell
Application.ScreenUpdating = True
MakeHeaders
End Sub

This portion of the macro :

For Each ws In wb.Worksheets
If ws.Name <> "Master" Then
ws.UsedRange.ClearContents
End If
Next

Will clear the existing TRUCk sheets each time you add new data and click the RUN button. If you do not delete the old data on the MASTER sheet,
the newly created sheets will include all of the old data plus the new data.

 

[Lazyh0rse]

Thanks, it seems that I do not have the skill to make that change
Would it be possible to make the change in workbook on your amazon drive?

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	Vehicle ID	Date	ODO Meter	Distance km	Liters - Depot	Result
2	Truck1	01/10/2018	14500	14500	171	0,00
3	Truck3	01/10/2018	32410	32410	164	0,00
4	Truck2	02/10/2018	20350	20350	333	0,00
5	Truck2	13/10/2018	20740	390	160	0,00
6	Truck1	15/10/2018	14800	300	143	0,00
7	Truck3	19/10/2018	32890	480	178	0,00
8	Truck3	19/10/2018	33450	560	243	0,00
9	Truck1	21/10/2018	15550	750	385	0,00
10	Truck2	22/10/2018	21570	830	370	0,00
11	Truck1	25/10/2018	15940	390	176	0,00
12	Truck3	26/10/2018	33980	530	226	0,00
13	Truck2	27/10/2018	21980	410	185	0,00
New

Cell Formulas
Range	Formula
D2	{=IF(COUNTIF(A$2:A2,A2)=1,C2,C2-SMALL(IF(A$2:A$13=A2,C$2:C$13),COUNTIF(A$2:A2,A2)-1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Logit]

.
New download link : https://www.amazon.com/clouddrive/share/hdSI5jIHS9KlSEK7uKQWQsn6jTqLps9hWxsyRYi585v