Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 -
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

Answer 1 / 2
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 459
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
Thank you

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

CCM 2942 users have said thank you to us this month

View all 7 comments
Report
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 459
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
Report
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019
Hello TrowaD;
Didn't work neither. Got an error.
Report
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!
Report
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.
Report
Posts
7
Registration date
Monday July 15, 2019
Status
Member
Last seen
August 26, 2019
Answer 2 / 2
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 459
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
Thank you

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

CCM 2942 users have said thank you to us this month

Report
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 !!!!!
Report
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 459
Thanks for the feedback Anitatelecon! Though I feel bad it took so long, I'm glad it all worked out.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!