Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    red 4/6/2009
    red 3/7/2009
    red 3/7/2009
    red 5/5/2009
    red 4/6/2009
    I need a formula, to return the most recent date entered for each color. The list below are in columns B and C, I need the range to cover the entire column as it continuously gets longer as I enter new colors (which are Locations)


    red 4/6/2009
    red 5/5/2009
    green 3/7/2009
    green 3/7/2009
    green 4/6/2009
    green 4/6/2009
    blue-5 3/7/2009
    blue-5 3/7/2009
    blue-5 5/5/2009
    blue-5 4/6/2009
    black 4/6/2009
    black 4/6/2009
    black 4/6/2009
    black 5/5/2009
    black 3/7/2009
    black 3/7/2009
    black 5/5/2009

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say you enter "red" (without the quotes) in F2, and the other color names below it.
    In G2, enter the following array formula (confirm with Ctrl+Shift+Enter):

    =MAX(IF($B$2:$B$65536=F2,$C$2:$C$65536))

    Fill down as far as needed.

    See the attached sample workbook.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='774549' date='10-May-2009 22:59']Let's say you enter "red" (without the quotes) in F2, and the other color names below it.
    In G2, enter the following array formula (confirm with Ctrl+Shift+Enter):

    =MAX(IF($B$2:$B$65536=F2,$C$2:$C$65536))

    Fill down as far as needed.

    See the attached sample workbook.[/quote]


    Array formula will affect the performance if you have a long list going into
    ten of thousands.

    Using Han's sample,

    here a shorter version, but this is still an array formula. ( confirm with Ctrl, Shift and Enter )

    =MAX((B2:B10000=F2)*(C2:C10000))

    another non array formula but you can't use whole column

    =LOOKUP(2,1/($B$2:$B$10000=$F2),$C$2:$C$10000)

    HTH
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I believe these formulas will solve my problem.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using this Array formula:
    {=MAX(IF(Tracker!$D$11:Tracker!$D$65488=D15,Tracke r!$P$11:Tracker!$P$65488))}

    If P11 to P65488 is blank, I get a date 1/0/1900. Instead of the date, how can I get NONE to be displayed in the cell?

    I tried:
    {=MAX(IF(Tracker!$D$11:Tracker!$D$65488=D15,Tracke r!$P$11:Tracker!$P$65488,"NONE"))}

    but no luck, the date still appears???

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    =IF(MAX(IF(Tracker!$D$11:Tracker!$D$65488=D15,Trac ker!$P$11:Tracker!$P$65488))=0,"None",MAX(IF(Track er!$D$11:Tracker!$D$65488=D15,Tracker!$P$11:Tracke r!$P$65488)))

    as an array formula, of course. It may be slow to recalculate, though.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No that's perfect and the speed is hardly noticeable.

    Thanks

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    [quote name='stans' post='798132' date='15-Oct-2009 15:34']... and the speed is hardly noticeable.[/quote]

    Now, that's what I call an ambiguous phrase!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •