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
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.
Say "Thank you"
Glad we were able to help! Love us? Write us a review! Rate CCM
6774 users have said thank you to us this month