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

Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019
-
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.
See more 

2 replies

Best answer
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
379
1
Thank you
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

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5622 users have said thank you to us this month

TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
379 -
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
anitatelecon
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019
-
Hello TrowaD;
Didn't work neither. Got an error.
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1571 > anitatelecon
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!
anitatelecon
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019
> ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
-
excuse me, ac3mark, but there is a screenshot that I sent.
anitatelecon
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019
-
Respond to TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
379
1
Thank you
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

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5622 users have said thank you to us this month

anitatelecon
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 !!!!!
TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
379 -
Thanks for the feedback Anitatelecon! Though I feel bad it took so long, I'm glad it all worked out.
Respond to TrowaD