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

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

"cardan" wrote:

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

OOPS,

I missed your 'numbers' are text

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

Don't forget the array instructions

Mike

"Mike H" wrote:

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

"cardan" wrote:

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

On Feb 19, 10:04*am, Mike H wrote:
OOPS,

I missed your 'numbers' are text

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

Don't forget the array instructions

Mike

"Mike H" wrote:
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

"cardan" wrote:

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

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?

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


"cardan" wrote:

On Feb 19, 10:04 am, Mike H wrote:
OOPS,

I missed your 'numbers' are text

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

Don't forget the array instructions

Mike

"Mike H" wrote:
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

"cardan" wrote:

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

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?

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

"Mike H" wrote:

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


"cardan" wrote:

On Feb 19, 10:04 am, Mike H wrote:
OOPS,

I missed your 'numbers' are text

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

Don't forget the array instructions

Mike

"Mike H" wrote:
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

"cardan" wrote:

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

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?

On Feb 19, 11:47*am, Mike H wrote:
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

"Mike H" wrote:
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

"cardan" wrote:

On Feb 19, 10:04 am, Mike H wrote:
OOPS,

I missed your 'numbers' are text

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

Don't forget the array instructions

Mike

"Mike H" wrote:
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

"cardan" wrote:

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

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?

Thanks Mike, I will give it a shot!