Finding the Max Date In Series

[cardan]

Hello,

I have a series of serial numbers (converted to text), dates and
amounts in column format. The number of serial numbers changes
depending on the date sold. The amount it sold for also changes. for
example

001 1-3-09 $300
001 1-5-07 $250
001 2-3-02 $550
002 4-3-01 $600
002 5-9-05 $300
003 6-9-07 $200

There are numerous serial numbers that repeat numerous times (about
5000 rows of code)

I would like to write a formula that finds the maximum date for the
001 for example and then returns the dollar figure in that row. Is
this a formula or do I need VBA?

Thank you for your time

 

[Mike H]

Hi,

You can do it with a formula

=VLOOKUP(MAX(IF(A1:A12=1,B1:B12)),B1:C12,2,FALSE)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[Mike H]

OOPS,

I missed your 'numbers' are text

=VLOOKUP(MAX(IF(A1:A12="001",B1:B12)),B1:C12,2,FALSE)

Don't forget the array instructions

Mike

 

[cardan]

OOPS,

I missed your 'numbers' are text

=VLOOKUP(MAX(IF(A1:A12="001",B1:B12)),B1:C12,2,FALSE)

Don't forget the array instructions

Mike

Hi Mike, Thank you for your response. I am getting a number but it is
not the correct one. Upon evaluating the formula, it seems to be
pulling the correct date, but there are multiple dates that are the
same so I think it is pulling the amount from the first date that
equals the max date for "001", regardless of the "001" criteria. Any
way to correct?

 

[Mike H]

Hi,

I'm sure this can be done with a formula but I'm stumped so heres a
function. Alt +F11 to open VB editor. Right click 'ThisWorkbook' and insert
module and paste the code in

Call with

=Bestrow(A1:A100,"001")

or use a cell reference for the text value

=bestrow(a1:a100,D1)

Note it will work anywhere in the sheet but always uses the 2 columns to the
right of the range you specify in the function call.

Function bestrow(rng As Range, lval As String)
For Each c In rng
If c.Value = lval Then
founddate = c.Offset(, 1).Value
If founddate >= bestdate Then
bestrow = c.Offset(, 2).Value
End If
End If
Next
End Function

Mike

 

[Mike H]

Sorry,

pasted a debug version, try this one

Function bestrow(rng As Range, lval As String)
For Each c In rng
If c.Value = lval Then
founddate = c.Offset(, 1).Value
If founddate >= bestdate Then
bestdate = founddate
bestrow = c.Offset(, 2).Value
End If
End If
Next
End Function

Mike

 

[cardan]

Sorry,

pasted a debug version, try this one

Function bestrow(rng As Range, lval As String)
For Each c In rng
If c.Value = lval Then
    founddate = c.Offset(, 1).Value
        If founddate >= bestdate Then
            bestdate = founddate
            bestrow = c.Offset(, 2).Value
        End If
End If
Next
End Function

Mike

Thanks Mike, I will give it a shot!