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.
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.
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 !
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.
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!
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
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
- 2591
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- February 11, 2020
396Sorry 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
Didn't work neither. Got an error.
- Posts
- 7
- Registration date
- Monday July 15, 2019
- Status
- Member
- Last seen
- August 26, 2019
> Blocked Profile