How to find the latest time per name in a table with names and times [Solved/Closed]

Report
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019
-
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
-
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.

2 replies

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
Hi Anitatelecon,

Sorry for the very late response, didn't mean to leave you hanging.

It looks to me as you would have to use a semi-colon instead of a comma. That would depend on the Excel regional version you have. I have to use the semi-colon, but most people here have to use the comma.

So try:
=HigestValue(E2;B:B)

Best regards,
Trowa
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019

Hello TrowaD;
Didn't work neither. Got an error.
Blocked Profile >
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019

On which line? Help him help you. Take a screen shot of the error and post it. Saying you got an error, and not saying what the error is, does not help!
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019
> Blocked Profile
excuse me, ac3mark, but there is a screenshot that I sent.
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019

Thanks Trowa. It worked. I only had to modify the results format to convert into date and I got the result I was looking for. Really appreciated your help !!!!!
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
Thanks for the feedback Anitatelecon! Though I feel bad it took so long, I'm glad it all worked out.