|
Finding the Max Date In Series
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 |
Finding the Max Date In Series
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 |
Finding the Max Date In Series
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 |
Finding the Max Date In Series
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? |
Finding the Max Date In Series
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? |
Finding the Max Date In Series
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? |
Finding the Max Date In Series
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! |
| All times are GMT +1. The time now is 03:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com