Formula Needed to Display Most Recent Date Entered

[Carrie Kaelin]

I need a way to extract the most recent accident date entered for a particular store. Example:

Store 1 12/02/03
Store 1 10/24/04
Store 1 01/01/05
Store 2 01/05/05
Store 2 10/31/05

In the above example, I would need the most recent date of 01/01/05 to be displayed for Store 1 and 10/31/05 for Store 2. Is there also a way if there are no accidents for a particular store that the cell will stay blank next to the store #?

Thank you for your assistance. -Carrie

 

[tactps]

Welcome

try:
=IF(MAX((A1:A5="Store 1")*B1:B5)=0,"",MAX((A1:A5="Store 1")*B1:B5))

Enter using Ctrl-Shift-Enter.

 

[Yogi Anand]

I need a way to extract the most recent accident date entered for a particular store. Example:

Store 1 12/02/03
Store 1 10/24/04
Store 1 01/01/05
Store 2 01/05/05
Store 2 10/31/05

In the above example, I would need the most recent date of 01/01/05 to be displayed for Store 1 and 10/31/05 for Store 2. Is there also a way if there are no accidents for a particular store that the cell will stay blank next to the store #?

Thank you for your assistance. -Carrie

Hi Carrie:

Welcome to MrExcel Board!

If I understand you correctly you can use the following array formula in cell E2 ...

=Max(IF($A$2:$A$6=D2,$B$2:$B$6))

this is then copied down

as illustrated in the following ...

Book1
	A	B	C	D	E
1	Store	Date
2	Store1	12/02/2003		Store1	01/01/2005
3	Store1	10/24/2004		Store2	10/31/2005
4	Store1	01/01/2005
5	Store2	01/05/2005
6	Store2	10/31/2005
7
Sheet1

 

[Carrie Kaelin]

Yogi:

Your formula worked great, but I thought of one more additional question. Can I add a condition to the if statement? If a store does not have any accidents it displays 01/0/1900 next to the store name. Can a condition be added to the MaxIf formula to enter the text "No Accidents" next to the store name.

My data is on a different worksheet. I modified the formula you provided to the following: =MAX(IF(Data!$A$1:$A$6000=A2,Data!$D$1:$D$6000)) where where A2 lists the store name on my current worksheet. If the store does not appear on our accident listing, I wanted "No Accident" listed beside the name. I attempted the following =MAX(IF(Data!$A$1:$A$6000=A2,Data!$D$1:$D$6000, "No Accident")) but it does not work.

Thanks again for any suggestions, Carrie

I need a way to extract the most recent accident date entered for a particular store. Example:

Store 1 12/02/03
Store 1 10/24/04
Store 1 01/01/05
Store 2 01/05/05
Store 2 10/31/05

In the above example, I would need the most recent date of 01/01/05 to be displayed for Store 1 and 10/31/05 for Store 2. Is there also a way if there are no accidents for a particular store that the cell will stay blank next to the store #?

Thank you for your assistance. -Carrie

Hi Carrie:

Welcome to MrExcel Board!

If I understand you correctly you can use the following array formula in cell E2 ...

=Max(IF($A$2:$A$6=D2,$B$2:$B$6))

this is then copied down

as illustrated in the following ...

Book1
	A	B	C	D	E
1	Store	Date
2	Store1	12/02/2003		Store1	01/01/2005
3	Store1	10/24/2004		Store2	10/31/2005
4	Store1	01/01/2005
5	Store2	01/05/2005
6	Store2	10/31/2005
7
Sheet1

 

[Yogi Anand]

Hi Carrie:

Try the following array formula ...

=IF(ISNA(MATCH(A2,data!$A$1:$A$6000,0)),"No Accident",MAX(IF(data!$A$1:$A$6000=A2,data!$D$1:$D$6000)))

 

[Carrie Kaelin]

Everything works perfect. Many thanks, Carrie

 

[Yogi Anand]

You Are Very Welcome Carrie ... Now Let Us Keep EXCELing!