Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! How do I return the latest of a series of dates using Vlooku
With nearly 3000 entries, and growing, could someone help me to make this
work more easily? Sorry, what I meant to say was, 'could someone help me to make this work'! I have a list of client references in one column of a s'sheet showing when clients invested. In another column, I have the dates when they invested; often there will be several dates over the last 5 years for each client. In the last column, and for each row of a client reference, I need to return the latest of the dates that relate to each client reference and this client list will continue to grow as additional investments are made, so the latest date will need to change to reflect the latest, last one added. As an example of what I think I'm looking to achieve... A B C Client Date Latest Ref Invested date X01 1/1/2006 1/1/2006 X09 3/5/2006 28/7/2007 X23 26/1/2007 26/1/2007 X78 19/3/2007 19/3/2007 X09 28/7/2007 28/7/2007 Is there a simple formula to use? I'm presuming that Vlookup is approapriate. Or am I expecting too much of Excel? I hope there's a super guru out there! Thanks Geoff. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! How do I return the latest of a series of dates using Vlooku
On Tue, 9 Oct 2007 03:36:00 -0700, Geoff Newham
wrote: With nearly 3000 entries, and growing, could someone help me to make this work more easily? Sorry, what I meant to say was, 'could someone help me to make this work'! I have a list of client references in one column of a s'sheet showing when clients invested. In another column, I have the dates when they invested; often there will be several dates over the last 5 years for each client. In the last column, and for each row of a client reference, I need to return the latest of the dates that relate to each client reference and this client list will continue to grow as additional investments are made, so the latest date will need to change to reflect the latest, last one added. As an example of what I think I'm looking to achieve... A B C Client Date Latest Ref Invested date X01 1/1/2006 1/1/2006 X09 3/5/2006 28/7/2007 X23 26/1/2007 26/1/2007 X78 19/3/2007 19/3/2007 X09 28/7/2007 28/7/2007 Is there a simple formula to use? I'm presuming that Vlookup is approapriate. Or am I expecting too much of Excel? I hope there's a super guru out there! Thanks Geoff. You can use the **array** formula in the form of: =MAX((A3=$A$3:$A$1000)*$B$3:$B$1000) To enter an **array* formula; after entering the formula into the cell or formula bar, confirm by holding down <ctrl<shift while you hit <enter. XL will place braces {...} around the formula. You can also use the NON-array formula: =SUMPRODUCT(MAX((A3=$A$3:$A$10)*$B$3:$B$10)) entering it normally. Note that you cannot use a reference to an entire column in these kinds of formulas. (e.g. you cannot use A:A) --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! How do I return the latest of a series of dates using Vlooku
Hi Geoff,
in the absence of a super guru, I can offer you this array* formula in C2: =MAX(IF((A$2:A$3000=A2),B$2:B$3000)) *As this is an array formula, then once you have typed it in (or subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it, rather than the usual ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Copy the formula down into C3:C3000 (or however many entries you have). Hope this helps. Pete On Oct 9, 11:36 am, Geoff Newham wrote: With nearly 3000 entries, and growing, could someone help me to make this work more easily? Sorry, what I meant to say was, 'could someone help me to make this work'! I have a list of client references in one column of a s'sheet showing when clients invested. In another column, I have the dates when they invested; often there will be several dates over the last 5 years for each client. In the last column, and for each row of a client reference, I need to return the latest of the dates that relate to each client reference and this client list will continue to grow as additional investments are made, so the latest date will need to change to reflect the latest, last one added. As an example of what I think I'm looking to achieve... A B C Client Date Latest Ref Invested date X01 1/1/2006 1/1/2006 X09 3/5/2006 28/7/2007 X23 26/1/2007 26/1/2007 X78 19/3/2007 19/3/2007 X09 28/7/2007 28/7/2007 Is there a simple formula to use? I'm presuming that Vlookup is approapriate. Or am I expecting too much of Excel? I hope there's a super guru out there! Thanks Geoff. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! How do I return the latest of a series of dates using Vlooku
NON array formula
=MAX(INDEX((A2:A100="a")*B2:B100,0)) Just hit Enter "Geoff Newham" wrote: With nearly 3000 entries, and growing, could someone help me to make this work more easily? Sorry, what I meant to say was, 'could someone help me to make this work'! I have a list of client references in one column of a s'sheet showing when clients invested. In another column, I have the dates when they invested; often there will be several dates over the last 5 years for each client. In the last column, and for each row of a client reference, I need to return the latest of the dates that relate to each client reference and this client list will continue to grow as additional investments are made, so the latest date will need to change to reflect the latest, last one added. As an example of what I think I'm looking to achieve... A B C Client Date Latest Ref Invested date X01 1/1/2006 1/1/2006 X09 3/5/2006 28/7/2007 X23 26/1/2007 26/1/2007 X78 19/3/2007 19/3/2007 X09 28/7/2007 28/7/2007 Is there a simple formula to use? I'm presuming that Vlookup is approapriate. Or am I expecting too much of Excel? I hope there's a super guru out there! Thanks Geoff. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help! How do I return the latest of a series of dates using Vl
Thanks, I'll give it a try later. Had some other responses, too, so I'll see
which I prefer. Thanks again Geoff. -- Geoff "Teethless mama" wrote: NON array formula =MAX(INDEX((A2:A100="a")*B2:B100,0)) Just hit Enter "Geoff Newham" wrote: With nearly 3000 entries, and growing, could someone help me to make this work more easily? Sorry, what I meant to say was, 'could someone help me to make this work'! I have a list of client references in one column of a s'sheet showing when clients invested. In another column, I have the dates when they invested; often there will be several dates over the last 5 years for each client. In the last column, and for each row of a client reference, I need to return the latest of the dates that relate to each client reference and this client list will continue to grow as additional investments are made, so the latest date will need to change to reflect the latest, last one added. As an example of what I think I'm looking to achieve... A B C Client Date Latest Ref Invested date X01 1/1/2006 1/1/2006 X09 3/5/2006 28/7/2007 X23 26/1/2007 26/1/2007 X78 19/3/2007 19/3/2007 X09 28/7/2007 28/7/2007 Is there a simple formula to use? I'm presuming that Vlookup is approapriate. Or am I expecting too much of Excel? I hope there's a super guru out there! Thanks Geoff. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Earliest and latest dates that can be correctly represented in Exc | Excel Discussion (Misc queries) | |||
Lookup / return latest date in range AND value of 1 column to the | Excel Worksheet Functions | |||
Finding the Latest Date from Several Dates in Different Columns | Excel Discussion (Misc queries) | |||
Return the latest date from a list of dates | Excel Worksheet Functions | |||
Function to return the latest non-zero value in a range of cells . | Excel Worksheet Functions |