User name and Time to sort

Posts
3
Registration date
Monday July 15, 2019
Status
Member
Last seen
July 25, 2019
- - Latest reply: TrowaD
Posts
2508
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 13, 2019
- Aug 6, 2019 at 11:51 AM
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 

1 reply

Posts
2508
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 13, 2019
356
0
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
anitatelecon
Posts
3
Registration date
Monday July 15, 2019
Status
Member
Last seen
July 25, 2019
-
Thanks Trowa. I have a problem with the part: Now in F2 type: =HigestValue(E2,B:B). I get a error in the fonction and I can't continue. Could you, please, help me with this? Thanbks a lot !
anitatelecon
Posts
3
Registration date
Monday July 15, 2019
Status
Member
Last seen
July 25, 2019
-
Hello TrowaD: I'm stuck with the issue I wrote before. Do you ahve a minute to look at it, please ? Thanks in advance !
TrowaD
Posts
2508
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 13, 2019
356 -
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
Respond to TrowaD