Return Most Recent Date Entered

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

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

Reply | Quote

Thanks, I believe these formulas will solve my problem.

Reply | Quote

Using this Array formula:
{=MAX(IF(Tracker!$D$11:Tracker!$D$65488=D15,Tracker!$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,Tracker!$P$11:Tracker!$P$65488,”NONE”))}

but no luck, the date still appears???

Reply | Quote

Try

=IF(MAX(IF(Tracker!$D$11:Tracker!$D$65488=D15,Tracker!$P$11:Tracker!$P$65488))=0,”None”,MAX(IF(Tracker!$D$11:Tracker!$D$65488=D15,Tracker!$P$11:Tracker!$P$65488)))

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

Reply | Quote

No that’s perfect and the speed is hardly noticeable.

Thanks

Reply | Quote

… and the speed is hardly noticeable.

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

Reply | Quote