Lookup for oldest date among different dates

Hi all,
I badly need your help in the below formula

Example:
Structure : Account includes Sub-accounts and each sub-account includes dials

Formula : Account activation date = Oldest Sub-account activation date
 where there is  more than one account and hence different activation 
date for each account and in turn the sub-accounts that belong to each account
 
Below the example

    A                  B                     C              D
Account #    Sub-Account #    Account    Sub-account  
                                         activation       activation 
                                            date               date          
   
 2                  2.11                   ??           21-01-2010
 2                  2.12                   ??           26-01-2010
 2                  2.13                   ??           03-02-2010
 3                  3.111                 ??            14-02-2010
 3                  3.112                 ??            29-04-2010

Account Activation date for account#2 shall be 21-01-2010 (Oldest 
sub-account activation date)

Many thanks,
 Khaled

0
Utf
5/11/2010 1:38:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
450 Views

Similar Articles

[PageSpeed] 38

Hi,

Try this ARRAY formula. Put it in C2, ARRAY enter it (see below) and drag down

=MIN(IF($A$2:$A$6=A2,$D$2:$D$6))

This is an array formula which must be entered by pressing 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 formula
you must enter it again with CTRL+Shift+Enter.


-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"khaled shaheen" wrote:

> Hi all,
> I badly need your help in the below formula
> 
> Example:
> Structure : Account includes Sub-accounts and each sub-account includes dials
> 
> Formula : Account activation date = Oldest Sub-account activation date
>  where there is  more than one account and hence different activation 
> date for each account and in turn the sub-accounts that belong to each account
>  
> Below the example
> 
>     A                  B                     C              D
> Account #    Sub-Account #    Account    Sub-account  
>                                          activation       activation 
>                                             date               date          
>    
>  2                  2.11                   ??           21-01-2010
>  2                  2.12                   ??           26-01-2010
>  2                  2.13                   ??           03-02-2010
>  3                  3.111                 ??            14-02-2010
>  3                  3.112                 ??            29-04-2010
> 
> Account Activation date for account#2 shall be 21-01-2010 (Oldest 
> sub-account activation date)
> 
> Many thanks,
>  Khaled
> 
0
Utf
5/11/2010 1:58:01 PM
Hi Mike,

Thanks for your fast response. It's returning the same oldest date 
(21-01-2010)for the 2 account numbers (1 & 2)   although the oldest date for 
each of them is different 

Please help 

"Mike H" wrote:

> Hi,
> 
> Try this ARRAY formula. Put it in C2, ARRAY enter it (see below) and drag down
> 
> =MIN(IF($A$2:$A$6=A2,$D$2:$D$6))
> 
> This is an array formula which must be entered by pressing 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 formula
> you must enter it again with CTRL+Shift+Enter.
> 
> 
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "khaled shaheen" wrote:
> 
> > Hi all,
> > I badly need your help in the below formula
> > 
> > Example:
> > Structure : Account includes Sub-accounts and each sub-account includes dials
> > 
> > Formula : Account activation date = Oldest Sub-account activation date
> >  where there is  more than one account and hence different activation 
> > date for each account and in turn the sub-accounts that belong to each account
> >  
> > Below the example
> > 
> >     A                  B                     C              D
> > Account #    Sub-Account #    Account    Sub-account  
> >                                          activation       activation 
> >                                             date               date          
> >    
> >  2                  2.11                   ??           21-01-2010
> >  2                  2.12                   ??           26-01-2010
> >  2                  2.13                   ??           03-02-2010
> >  3                  3.111                 ??            14-02-2010
> >  3                  3.112                 ??            29-04-2010
> > 
> > Account Activation date for account#2 shall be 21-01-2010 (Oldest 
> > sub-account activation date)
> > 
> > Many thanks,
> >  Khaled
> > 
0
Utf
5/16/2010 8:17:01 PM
Mike H's formula does gives the correct result, if you follow Mike's
instructions to press CTRL+Shift+Enter. (Select the formula cell,
click F2 and press CTRL+Shift+Enter, and drag down the formula)

Regards,
Per







On 16 Maj, 22:17, khaled shaheen
<khaledshah...@discussions.microsoft.com> wrote:
> Hi Mike,
>
> Thanks for your fast response. It's returning the same oldest date
> (21-01-2010)for the 2 account numbers (1 & 2) =A0 although the oldest dat=
e for
> each of them is different
>
> Please help
>
>
>
> "Mike H" wrote:
> > Hi,
>
> > Try this ARRAY formula. Put it in C2, ARRAY enter it (see below) and dr=
ag down
>
> > =3DMIN(IF($A$2:$A$6=3DA2,$D$2:$D$6))
>
> > This is an array formula which must be entered by pressing CTRL+Shift+E=
nter
> > and not just Enter. If you do it correctly then Excel will put curly br=
ackets
> > around the formula {}. You can't type these yourself. If you edit the f=
ormula
> > you must enter it again with CTRL+Shift+Enter.
>
> > --
> > Mike
>
> > When competing hypotheses are otherwise equal, adopt the hypothesis tha=
t
> > introduces the fewest assumptions while still sufficiently answering th=
e
> > question.
>
> > "khaled shaheen" wrote:
>
> > > Hi all,
> > > I badly need your help in the below formula
>
> > > Example:
> > > Structure : Account includes Sub-accounts and each sub-account includ=
es dials
>
> > > Formula : Account activation date =3D Oldest Sub-account activation d=
ate
> > > =A0where there is =A0more than one account and hence different activa=
tion
> > > date for each account and in turn the sub-accounts that belong to eac=
h account
>
> > > Below the example
>
> > > =A0 =A0 A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0B =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 C =A0 =A0 =A0 =A0 =A0 =A0 =A0D
> > > Account # =A0 =A0Sub-Account # =A0 =A0Account =A0 =A0Sub-account =A0
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0activation =A0 =A0 =A0 activation
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 date =A0 =A0 =A0 =A0 =A0 =A0 =A0 date =A0 =A0 =A0 =A0 =
=A0
>
> > > =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02.11 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 ?? =A0 =A0 =A0 =A0 =A0 21-01-2010
> > > =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02.12 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 ?? =A0 =A0 =A0 =A0 =A0 26-01-2010
> > > =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02.13 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 ?? =A0 =A0 =A0 =A0 =A0 03-02-2010
> > > =A03 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03.111 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 ?? =A0 =A0 =A0 =A0 =A0 =A014-02-2010
> > > =A03 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03.112 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 ?? =A0 =A0 =A0 =A0 =A0 =A029-04-2010
>
> > > Account Activation date for account#2 shall be 21-01-2010 (Oldest
> > > sub-account activation date)
>
> > > Many thanks,
> > > =A0Khaled- Skjul tekst i anf=F8rselstegn -
>
> - Vis tekst i anf=F8rselstegn -

0
Per
5/16/2010 9:11:46 PM
Reply:

Similar Artilces:

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

filtering data into different workheet #2
Thanks for the help but i managed to find my way using pivot tables. Thanks again Swmasso -- swmasso ----------------------------------------------------------------------- swmasson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=838 View this thread: http://www.excelforum.com/showthread.php?threadid=27045 ...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

download dates are wrong
In Money 2006 I pay my bills on my banks website(Bofa Mass)when I download them to money the dates are changed..Example I told bank to make payment on 10/16/06 when I download in money it gets changed to 10/10/06..Any help would be greatly appreciated... There's an option in Tools - Settings -OnLine Services - to overwrite transaction dates with downloaded ones. Is it checked? -- Michael Gordon "Scoop" <Scoop@discussions.microsoft.com> wrote in message news:24ADE01B-D4DF-4477-8B29-D065B85475C7@microsoft.com... > In Money 2006 I pay my bills on my banks website(...

Date format issue #2
Hello, I have date's that come to me in this format: 20040424 20040426 20040428 how can I convert them so EXCEL can read that as a date? Note: Excel does not recognize that format when you format/cells/number/date Thank -- Message posted from http://www.ExcelForum.com No, Excel will se it as text or as a number. You will need to transform it into a new column =Date(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "kkondrat1 >" <<kkondra...

Charting data against dates where dates are not at fixed intervals
I have several data points that are the result of a pivot-table and I'm trying to plot them in a pivot-chart (I can easily get them out of the pivot table if the graph format is not supported for pivot-charts). Here's an example of the data: 2005/01/15 2005/02/22 2005/03/18 2005/05/20 X 16 18 22 12 Y 4 6 3 2 Z 14 19 12 34 What I'd like to do is plot the data (preferably on a line chart) such that ...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Default on date control shows yesterday.
The default date on many date control shows yesterday. The Crm Tools | Options Time zone is set to (GMT +10) Canberra, Melbourne, Sydney. The user formats and system formats are set to English-Australia. The computer clock and timezone settings are correct. Done iisreset. Any ideas? Hi Bill, I remembered there might be a patch that you need to install for Austrialia time zone. You might want to do a search in the MBS KB to see if it applies to you. Darren Liu, Microsoft CRM MVP Crowe http://www.crowecrm.com On Aug 12, 6:06=A0pm, Bill Altmann <BillAltm...@discussions.microsoft.com...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

linking tables from different Access templates?
Hello I have been playing with Access 2007 and am at the stage of almost being ready to ditch the "toy" database and prepare the real thing. I have been working on a database set up for us which has worked well but is showing its limitations (and highlighting mine!) I like the look of the Access 2007 Contacts and Events templates which, with some tweaking, could work well for us. My question is - is it possible to link the Contacts table with the Events table created in the templates? At the moment, I have an Events table (which includes various information abo...

Selecting a range of cells based on the date.
Hi MS Office Help, I would like to know how to choose a selection of cells based on what month it is. For instance, if the month was january, excel would allow me to select cells A1-F1, which I could then use as a data source for a chart. If the month became march or april, excel would allow me to select cells C1-H1, D1-I1 respectively, and so on. This will allow me to vary my chart automatically as the month changes. Help is greatly appreciated. Hi Solomon, Use the Data - Autofilter -custom feature or Data - filter - text filters - custom depending on your version of...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

date conditional formatting?
Hi, I will try my best to explain. I need to have conditional formatting as such: 3 cells in adjacent columns. first has name, second has text missed punch, third has date of error. I need the 3 cells to be removed(possibly to another worksheet in same workbook) when the date matures to one year old? Hmm. Did I make sense? Thanks so much Luke Oops, Excel 2003 "Frank" <frankl@IHATESPAMworldnet.att.net> wrote in message news:%23DXNloweHHA.1244@TK2MSFTNGP04.phx.gbl... > Hi, I will try my best to explain. I need to have conditional formatting > as such: > 3 cell...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Repost- account list balance and ending balance different amounts (in MONEY 2004
When I click Account list my bank balance shows an amount say "A" dollars.. but when i go to the particular account the ending balance is not "A" dollars but "B" dollars. Why is there a discrepency?? Also the cash flow takes the "A" dollars into account while computing instead of the current ending balance "B". The difference is "B-A=C dollars" Could it be because i cancelled a electronic payment of "C" dollars and marked it void in the account and then deleted it later thanks nithya ...

Importing Entourage Date
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop The board on my old MacBook has just died but I managed to clone the disc and drag all the data out and onto an external drive. All my data is there along with what seems to be the complete system however I can't import the Entourage files, database and folders into my iMac! I've tried to locate the folders and files using import then searching through the Documents / Office 2008 identities etc. but when I get to the database folders they are 'greyed' out and I can't attach th...

changing data only after a certain date
Here is my dilemma. Right now in tracking on time delivery, we use a lead time field to determine if we gave a vendor enough time. I have historical data that includes this lead time. The issue i have is that if we change the lead time, it changes even for orders we already received with a previous lead time. Example PO 123 was received on 7/1/2009. The lead time table shows a lead time of 10 days. PO Part Entered Due Received Late 123 abc 6/1/2009 6/15/2009 7/1/2009 Yes I have a query that asks if the due date was within the lead time. If i we...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Count occurrences of dates
Column A is a list of dates with the format 01/01/2000, 30/04/2001 etc. How would I count the number of occurrences of dates in column A for the month of say January 2000 How would I count the number of occurrences of dates for the year 2000 Great If you can help. George Gee Hello George, For January 2000 you could use a formula like =SUMPRODUCT(--(TEXT(A1:A100,"mmmyyyy")="Jan2000")) for the whole year =SUMPRODUCT(--(YEAR(A1:A100)=2000)) extend the range as necessary but you can't use the whole column unless you have Excel 2007 Hi Barry Thanks for your re...

Lookup #4
I am trying to label my bank info that I import into Excel. I have a column with the charge amount and a column with the recipient. I think a lookup table would work for this, but I need to have it check for wildcards. For example, the grocery chain is numbered here, so one might be GROCER 5454 and one may be GROCER 8724, so I need the function to look for GROCER*, right? Also, if it does not fit any category, how can I get it to use "Other"? Do I make that one just "*"? Mine is not working very well. Maybe my syntax is wrong. Any ideas? TIA Is there anyway you can st...

Outlook client going offline, time difference
HI I have a exchange 2000 server. Most of users have office xp and 2003. I would like some help on the following issues 1.Some times the outlook client goes offline. If i try to hit the send/receive button, it comes with a error saying some object not found. THe only remedy i know is to delete the entire profile and recreate the profile again to make it online. 2. On a particular 2003 outlook client, the time the messages are received it totally different from the actual time. I checked the system time and The system time is correct. If i send an email to this person at 2PM , on the ...

Filter message by reverse domain lookup
Hello all, I am kinda new to Exchange and was wondering if there was a function built into Exchange 2000 that would "look" at the source domain of the email message and then perform a "reverse" DNS lookup to verify that the domain actually exists and if it doesn't then have Exchange discard the email without sending an NDR? Is this possible strait out of the box? It seems like I remember iMail gateway having this feature. I am trying to filter out some of the spam that fills up my users inboxes. Thanks in advance. Jeremy jsteger@bellsouth.net (Jeremy Steger) wrote...

Can you change the default lookup from Account to Contact?
Is it possible to set the lookup for a customer field to default on "contacts" rather then "accounts?" We do more business with contacts so it is a pain to have to change this all the time. there is no supported way to do this in the current release -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Martin Flaherty" <mflaherty@techpg.com> wrote in message news:ucdNM74uEHA.3416@TK2MSFTNGP09.phx.gbl... > Is it possible to set the lookup for a customer field to default on > "contac...