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
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
Didn't work neither. Got an error.