Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A formula needed - probably an array formula
I have a database with hundreds of names - some repeat customers - which
is set up as follows: Column A Column B Column C Column D Dates First Names Last Names A Formula?? - Each row in the database has Date, First Name, Last Name. (More added each day) - the same name is often used several times during the year - hence another row in the database. - is it possible to have a formula in Column D that will say "yes" if it is the most recent(latest date) transaction for that name for the year so far or "no" if there is another transaction for that same name farther down (later date) in the year. Important! - if a transaction for John Doe is entered, for example, on Feb 5, the formula needs to say "Yes" in Column D. But if a new transaction, for John Doe again, is entered on May 23, then the one on Feb 5 needs to change to "No" since is no longer the most recent transaction (for John Doe)PLUS the May 23rd transaction now needs to say "Yes" - until(if there is one) a later one is entered, in which case, it will to change to "No". Thanks for any help Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A formula needed - probably an array formula
From: Ron Rosenfeld
Subject: A Stickler of a problem Date: Wed, 27 Aug 2003 22:13:45 -0400 Message-ID: References: On Wed, 27 Aug 2003 20:28:49 -0500, "Jim" wrote: Is this possible? Column A Column B Column C 100's of Different Dates 100's of Different First Names Last Names(100's of them) - Each row in the database has Date, First Name, Last Name. (More added each day) - a name is often used several times during the year - hence another row in the database. - is it possible to have a formula in Column D that will say "yes" if it the most recent(last) transaction for that name for the year so far or "no" if there is another transaction for that same name farther down in the year. thanks for any help!! Jim, again! If your three ranges are named Dates, First and Last, then *array-enter* the formula: =IF(A2=MAX((First=B2)*(Last=C2)*Dates),"Yes","No") into D2 and copy down as far as necessary. To array-enter a formula, hold down <ctrl<shift while hitting <enter. XL will place braces {...} around the formula. --ron ----------- Formula, written correctly, will adjust to changing conditions. -- Regards, Tom Ogilvy Jim wrote in message ... I have a database with hundreds of names - some repeat customers - which is set up as follows: Column A Column B Column C Column D Dates First Names Last Names A Formula?? - Each row in the database has Date, First Name, Last Name. (More added each day) - the same name is often used several times during the year - hence another row in the database. - is it possible to have a formula in Column D that will say "yes" if it is the most recent(latest date) transaction for that name for the year so far or "no" if there is another transaction for that same name farther down (later date) in the year. Important! - if a transaction for John Doe is entered, for example, on Feb 5, the formula needs to say "Yes" in Column D. But if a new transaction, for John Doe again, is entered on May 23, then the one on Feb 5 needs to change to "No" since is no longer the most recent transaction (for John Doe)PLUS the May 23rd transaction now needs to say "Yes" - until(if there is one) a later one is entered, in which case, it will to change to "No". Thanks for any help Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A formula needed - probably an array formula
Jim,
You've posted this same question now three different times and received multiple answers. It looks as if you are unable to find responses to your questions so I'm sending this also by email. Try either figuring out your newsreader, or doing a Google search on the newsgroup looking for the stuff you've posted. ====================================== On Sun, 31 Aug 2003 15:22:54 -0500, "Jim" wrote: I have a database with hundreds of names - some repeat customers - which is set up as follows: Column A Column B Column C Column D Dates First Names Last Names A Formula?? - Each row in the database has Date, First Name, Last Name. (More added each day) - the same name is often used several times during the year - hence another row in the database. - is it possible to have a formula in Column D that will say "yes" if it is the most recent(latest date) transaction for that name for the year so far or "no" if there is another transaction for that same name farther down (later date) in the year. Important! - if a transaction for John Doe is entered, for example, on Feb 5, the formula needs to say "Yes" in Column D. But if a new transaction, for John Doe again, is entered on May 23, then the one on Feb 5 needs to change to "No" since is no longer the most recent transaction (for John Doe)PLUS the May 23rd transaction now needs to say "Yes" - until(if there is one) a later one is entered, in which case, it will to change to "No". Thanks for any help Jim --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Help Needed | Excel Worksheet Functions | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
Array formula expertise needed | Excel Worksheet Functions | |||
Array formula needed | Excel Worksheet Functions | |||
Array formula needed | Excel Worksheet Functions |