How to get First timing & Last timing of the day through Looping

I have a list of Door entry timing with multiple date. So I am trying to find the first entry timing of the day and the last entry timing of the same day.

Column D & E is done being manually input. I wish to use VBA loop to do it.

By using the Normal excel function, i have tried using the function LARGE & SMALL to find the first and last timing of the day.

Example: LARGE(IF(A1 = A1:A100,ROW(A1:A100)),1) which would return me the Largest row then I use INDEX.

Therefore I am wondering if I can do the same or is there a simpler way to do it in VBA so I can get the result in Column D & E.

So Far this is how far i got:

Sub testing2()

Dim dateList As Range

newLR = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row

dateList = Sheet3.Range("a1:a" & newLR).Value

For x = 1 To newLR
    If Sheet3.Cells(x, 1) = dateList Then
        Sheet3.Cells(x, 4) = 3
    End If
Next x



End Sub

Solution

Not sure why you want to do it with VBA if it can be easily done using a pivot table or a formula, if you have Excel 365:

=LET(
    dates,(A2:A30),
    times,(B2:B30),
    days, UNIQUE(dates),
    earliest, BYROW(days, LAMBDA(day, MIN(IF(dates=day, times)))),
    latest, BYROW(days, LAMBDA(day, MAX(IF(dates=day, times)))),
    result, HSTACK(days, earliest, latest),
    result
)

or in a slightly different way:

=LET(
    dates, A2:A30,
    times, B2:B30,
    days, UNIQUE(dates),
    earliest, MAP(days, LAMBDA(day, MINIFS(times, dates, day))),
    latest, MAP(days, LAMBDA(day, MAXIFS(times, dates, day))),
    HSTACK(days, earliest, latest)
)

or perhaps:

=LET(
    data, A2:B30,
    days, UNIQUE(INDEX(data,,1)),
    times, LAMBDA(day, FILTER(INDEX(data,,2), INDEX(data,,1)=day)),
    earliest, BYROW(days, LAMBDA(day, MIN(times(day)))),
    latest, BYROW(days, LAMBDA(day, MAX(times(day)))),
    HSTACK(days, earliest, latest)
)

or if you want it with two columns:

=LET(
    dates,(A2:A30),
    times,(B2:B30),
    days, UNIQUE(dates),
    earliest, BYROW(days, LAMBDA(day, MIN(IF(dates=day, times)))),
    latest, BYROW(days, LAMBDA(day, MAX(IF(dates=day, times)))),
    repeatedDays, VSTACK(days, days),
    inout, VSTACK(earliest, latest),
    result, HSTACK(repeatedDays, inout),
    result
)