Average

Dim emp As Range, cemp As Range, rdata As Range, j As Long, rowscount As Long
Dim k As Long, filt As Range, r As Range
Dim cfind As Range
Dim empid As Long
Dim aveage_of_the_amount_paid_col_C As Double
Dim reimbusement_amount_col_D As Double
Dim number_of_miles_driven_col_E As Double

Sub A_MAIN_MACRO()
Application.ScreenUpdating = False
undo
unqemp
Worksheets("mileage").Activate

Set emp = Range(Range("M2"), Range("M2").End(xlDown))
Set rdata = Range(Range("A2"), Cells(Rows.Count, "E").End(xlUp))
For Each cemp In emp
rowscount = 0
rdata.AutoFilter field:=1, Criteria1:=cemp.Value
Set filt = rdata.SpecialCells(xlCellTypeVisible)

filt.Copy Worksheets("filter").Range("A1")
ffilter
Worksheets("filter").Cells.Clear

rdata.AutoFilter
    
Next cemp
Range("J1:M1").EntireColumn.Delete
Worksheets("result").Activate
MsgBox "mcro over"
Application.ScreenUpdating = True
End Sub

Sub unqemp()
Worksheets("mileage").Activate
Set emp = Range(Range("A2"), Cells(Rows.Count, "A").End(xlUp))
 Range("a2").Copy
 Range("J1").PasteSpecial
 Range("M1").PasteSpecial
Range("J2") = ">0"

emp.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "J1:J2"), CopyToRange:=Range("M1"), Unique:=True
Range(Range("M1"), Range("M1").End(xlDown)).Copy Worksheets("result").Range("A1")

End Sub

Sub ffilter()
Dim miles As Long, n As Long, m As Long, mmiles As Range
With Worksheets("filter")
'empid = Range("a2").Value
rowscount = .Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
For k = 2 To rowscount
.Cells(k, "F") = Month(.Cells(k, "B"))
Next k
'Range("g1").EntireRow.Cells.Clear
'Range("G1") = "distinct monthst"
For k = 2 To rowscount
''msgbox k
If .Cells(k + 1, "F") <> .Cells(k, "F") Then
n = n + 1
   ' .Cells(k, "G") = n
      m = m + 1
    Else
     miles = 0
     'n = 0
      For m = k To rowscount
        
       ' 'msgbox miles
miles = miles + .Cells(m, "E")
' n = n + 1
' 'msgbox miles
 If .Cells(m + 1, "F") <> .Cells(m, "F") Then Exit For
 Next m
 n = n + 1
 ' 'msgbox m
  ''msgbox k
'.Cells(k, "G") = n
k = m

''msgbox k
End If
Next k

'msgbox n
empid = .Range("a2")
aveage_of_the_amount_paid_col_C = WorksheetFunction.Sum(.Range("C1").EntireColumn) / n
reimbusement_amount_col_D = WorksheetFunction.Sum(.Range("E1").EntireColumn) / n
number_of_miles_driven_col_E = WorksheetFunction.Sum(.Range("d1").EntireColumn) / n



With Worksheets("result")
Set cfind = .Columns("A:A").Find(what:=empid, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.Offset(0, 1) = aveage_of_the_amount_paid_col_C
cfind.Offset(0, 2) = reimbusement_amount_col_D
cfind.Offset(0, 3) = number_of_miles_driven_col_E
End If
End With
.Cells.Clear

End With

End Sub

Sub undo()
With Worksheets("result")
.Cells.Clear
End With

End Sub