[COLOR="Navy"]Sub[/COLOR] MG17Aug19
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dic [COLOR="Navy"]As[/COLOR] Object, Bk [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] P [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] ray(), Temp2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tb [COLOR="Navy"]As[/COLOR] Double, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("B4"), .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
temp = IIf(Dn.Offset(, -1).Value = "", temp, Dn.Offset(, -1).Value)
[COLOR="Navy"]If[/COLOR] Dn.Offset(, 2) <> Temp2 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]If[/COLOR] Not Dic.exists(temp) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Dic(temp) = CreateObject("Scripting.Dictionary")
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]If[/COLOR] Len(Dn.Value) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]If[/COLOR] Not Dic(temp).exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
ReDim ray(1 To Rng.Count)
Dic(temp).Add (Dn.Value), Array(ray, Dn.Offset(, 2), 1, "")
[COLOR="Navy"]Else[/COLOR]
Q = Dic(temp).Item(Dn.Value)
Q(0)(Q(2)) = Dn.Offset(, 2) - Dn.Offset(-1, 3)
Q(2) = Q(2) + 1
Q(3) = Dn.Offset(, 3)
Dic(temp).Item(Dn.Value) = Q
[COLOR="Navy"]End[/COLOR] If
Temp2 = Dn.Offset(, 2)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
.Cells(1, 1).Resize(, 7) = Array("Rep Name", "Date", "Total Hours Worked", "Total Break Time", "1st log in", "Last log out", "Total number of breaks")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] P [COLOR="Navy"]In[/COLOR] Dic(k)
c = c + 1
.Cells(c, "A") = k
.Cells(c, "B") = P
.Cells(c, "C") = Format(Dic(k).Item(P)(3) - Dic(k).Item(P)(1), "h:mm")
.Cells(c, "E") = Format(Dic(k).Item(P)(1), "h:mmAM/PM")
.Cells(c, "F") = Format(Dic(k).Item(P)(3), "h:mmAM/PM")
[COLOR="Navy"]For[/COLOR] R = 1 To UBound(Dic(k).Item(P)(0))
[COLOR="Navy"]If[/COLOR] Len(Dic(k).Item(P)(0)(R)) [COLOR="Navy"]Then[/COLOR]
Num = Num + 1: oMax = Application.Max(oMax, Num)
.Cells(1, "G").Offset(, Num) = "Break (" & Num & ")"
.Cells(c, "G").Offset(, Num) = Format(Dic(k).Item(P)(0)(R) * 24 * 60, "0") & " Mins"
Tb = Tb + Dic(k).Item(P)(0)(R)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
.Cells(20, 1) = Tb
.Cells(c, "D") = Tb * 24 * 60 & " Mins"
.Cells(c, "G") = Num: Num = 0
Tb = 0
[COLOR="Navy"]Next[/COLOR] P
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1").Cells(1, 1)
[COLOR="Navy"]With[/COLOR] .Resize(c, oMax + 7)
.Borders.Weight = 2
.Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]