PDA

View Full Version : [SOLVED] Assistance with finding the last value



swisscheez
08-18-2005, 04:49 PM
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
08-18-2005, 05:24 PM
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
08-18-2005, 05:39 PM
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
08-18-2005, 05:54 PM
You're Welcome :beerchug:

Take Care