Assistance with finding the last value

[swisscheez]

i'm working on creating a "report" using data imported in to excel and need some assistance from this group if at all possible.
what I want to be able to do is summarize something like this ...
DATEMARKETASSOCIATEJOB TITLEJOB STATUS CREDIT PRODUCTION
Jan-05STATESMITH, JANEBDOITN $ 5,000.00
Jan-05STATEDOE, JOHNCMACT $ 1,000.00
Jan-05STATEEXAMPLE, A.CMACT $ 2,000.00
Jan-05STATEJONES, BCMACT $ 4,500.00
Feb-05STATESMITH, JANECMACT $ 7,500.00
Feb-05STATEDOE, JOHNCMITN $ 9,000.00
Feb-05STATEEXAMPLE, A.CMITN $ 10,000.00
Feb-05STATEJONES, BCMITN $ 4,000.00
Mar-05STATESMITH, JANECMITN $ 3,500.00
Mar-05STATEDOE, JOHNBDOITN $ 3,200.00
Mar-05STATEEXAMPLE, A.CMACT $ 6,540.00
Mar-05STATEJONES, BCMACT $ 6,548.00
and I can do this just fine except for one thing.
Using Jane Smith as an example, you can see that her job title changes over time. I want to be able to sum all of Jane Smith's ytd data and grab her last or most recent job code. I'm just not how to do that last part. It probably is very easy but i'm just not getting it.
Thank you for you time.
Lance

[Jacob Hilderbrand]

How about this UDF.

Option Explicit
 
Public Function LastValue(StrName As String, ColOffset As Long) As Double
Dim Cel             As Range
With Range("A:A")
        Set Cel = .Find(What:=StrName, LookIn:=xlValues, LookAt:=xlWhole, _
            MatchCase:=True, SearchDirection:=xlPrevious, After:=Range("A1"))
        If Not Cel Is Nothing Then
            LastValue = Val(Cel.Offset(0, ColOffset).Value)
        End If
    End With
    Set Cel = Nothing
End Function
 
Sub Macro1()
Dim x               As Double
x = LastValue("Smith", 1)
    MsgBox x
End Sub

You can also use the function in a worksheet cell.

ColOffset is the number of columns you want to offset to get the value. So if the names are in column A and the values are in column B, then the ColOffset would be 1.

[swisscheez]

Wow this was very close to what i needed. THANK YOU SO MUCH.
I needed to return a string rather than a number. so, fixed that on the function then removed the "Val" from the offset item.

Public Function LastValue(StrName As String, ColOffset As Long) As string
Dim Cel As Range
With Range("C:C")
        Set Cel = .Find(What:=StrName, LookIn:=xlValues, LookAt:=xlWhole, _
        MatchCase:=True, SearchDirection:=xlPrevious, After:=Range("C1"))
        If Not Cel Is Nothing Then
            LastValue = (Cel.Offset(0, ColOffset).Value)
        End If
    End With
    Set Cel = Nothing
End Function

[Jacob Hilderbrand]

You're Welcome

Take Care