How to find the latest time per name in a table with names and timesSolved/Closed

Question

Hello;
I have a sheet with a list of "use name" and "time" (which is a login date). I need to find for each user the latest date . But users have several Time (dates). I need to get for each user only the newest date. How can I can do that? See screenshot.

TrowaD · Answer

Hi Anitatelecon,

I noticed 2 things in your data.
- The dates are in the column to the right of the names.
- The dates are ordered A->Z.

Under those conditions I created a custom function for you.

First implement the code below in a standard module (Alt+f11 to open VBA window, click Insert on top menu, click on Module and paste the code in the big white field):
Function HighestValue(NameToSearch As String, ListOfNames As Range)
Dim lRow, x As Long

lRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row

For x = lRow To 1 Step -1
    If Cells(x, MyRange.Column) = sValue Then
        HighestValue = Cells(x, MyRange.Column + 1).Value
        Exit Function
    End If
Next x
End Function

Now you got acces to the function HigestValue(NameToSearch,ListOfNames).
 
As an example of how to use this function: Place the unique names of column B in column E (Copy column B to E, select column E, go to Data ribbon and click on Remove Duplicates).
Now in F2 type: =HigestValue(E2,B:B)
Drag the formula down as far as you have unique values in column E.

Let us know how this works out.

Best regards,
Trowa

TrowaD · Answer

Hi Anitatelecon,

Apparently I didn't change the declared names going from sub to function.

Could you replace the code with the one below and see how that goes:
Function HighestValue(NameToSearch As String, ListOfNames As Range)
Dim lRow, x As Long

lRow = Cells(Rows.Count, ListOfNames.Column).End(xlUp).Row

For x = lRow To 1 Step -1
    If Cells(x, ListOfNames.Column) = NameToSearch Then
        HighestValue = Cells(x, ListOfNames.Column + 1).Value
        Exit Function
    End If
Next x
End Function
 
Best regards,
Trowa

How to find the latest time per name in a table with names and times

2 responses

Similar discussions

Subscribe To Our Newsletter!