How to find the latest date in a table with given parameters

[YukonBrad]

Hi Again,

I'm back, stumped again, hoping for some help.

I have a spreadsheet where i track injuries and losses and the table has the the following columns

DATE LOCATION LOSS (Injury, Environmental, Vehicle) //others//



The data goes back for about three years and what I would like to be able to do is write a formula that would tell me the latest date for each location for each loss type.


For instance, the DATE of the last INJURY that LOCATION "A" had = [then the formula that i can't figure out]


Hope this makes sense. Thanks for your help.

--Brad

 

[rallcorn]

Assuming
Date Col A
Location Col B
H1 is location for which you're looking up last date
I1 =MAX(IF(B:B=H1,A:A)) ARRAY formula after typing formula press Ctrl+Shift+Enter instead of pressing "Enter". {} will automatically be placed around formula - don't type {} .

 

[YukonBrad]

HI rallcorn,
That worked but i need a little more, i need to know the location and LOSS type. Your formula gave me the latest date of loss for the location in question but I also was hoping to see the latest date for injury by location.

So using your assumptions

Date Col A
Location Col B
Loss type Col C
H1 would be location
L1 would be loss type

Date of last L1 in H1 =. . . ideas?

Thanks again.
--Brad

 

[rallcorn]

For instance, the DATE of the last INJURY that LOCATION "A" had

The solution I provided answered this request.

I also was hoping to see the latest date for injury by location

I'm confused!

Do you want to see all Locations with a list of loss types for that have occurred for each location along with the date of the most recent time each loss occurred at that location?

or
Do you want to see all Loss Types with a list of the locations at which they have occurred along with the most recent date the loss type occurred at each locaion?

or
Do you want to be able to pick ONE location with a list of loss types for that have occurred for at that location along with the date of the most recent time each loss occurred at that location?

I'd probably approach any of the above with a pivot table.
Place Location and Loss Type in the Row Labels section (in whichever order you need).

Place DATE in the Values section (change the Value Field Setting to MAX and Number Format to Date).

This shows by Location each loss type and most recent occurrence

Max of Date
Location	Loss Type	Total
A	Environmental</SPAN>	3/1/2011</SPAN>
	Injury</SPAN>	3/2/2011</SPAN>
	Other</SPAN>	3/4/2011</SPAN>
	Vehicle</SPAN>	3/3/2011</SPAN>
B	Environmental</SPAN>	4/1/2011</SPAN>
	Injury</SPAN>	4/2/2011</SPAN>
	Other</SPAN>	4/4/2011</SPAN>
	Vehicle</SPAN>	4/3/2011</SPAN>
C	Environmental</SPAN>	5/1/2011</SPAN>
	Injury</SPAN>	5/2/2011</SPAN>
	Other</SPAN>	5/4/2011</SPAN>
	Vehicle</SPAN>	5/3/2011</SPAN>

<TBODY>
</TBODY>

This shows by Loss Type the most recent occurrence at each location where the loss type has happened.

Max of Date
Loss Type	Location	Total
Environmental	A</SPAN>	3/1/2011</SPAN>
	B</SPAN>	4/1/2011</SPAN>
	C</SPAN>	5/1/2011</SPAN>
Injury	A</SPAN>	3/2/2011</SPAN>
	B</SPAN>	4/2/2011</SPAN>
	C</SPAN>	5/2/2011</SPAN>
Other	A</SPAN>	3/4/2011</SPAN>
	B</SPAN>	4/4/2011</SPAN>
	C</SPAN>	5/4/2011</SPAN>
Vehicle	A</SPAN>	3/3/2011</SPAN>
	B</SPAN>	4/3/2011</SPAN>
	C</SPAN>	5/3/2011</SPAN>

<TBODY>
</TBODY>

If you want to show just a single location, drag Location from Row Labels section to Report Filter section and select the single Location you wish to see:

Location</SPAN>	A</SPAN>
Max of Date
Loss Type	Total
Environmental</SPAN>	3/1/2011</SPAN>
Injury</SPAN>	3/2/2011</SPAN>
Other</SPAN>	3/4/2011</SPAN>
Vehicle</SPAN>	3/3/2011</SPAN>

<TBODY>
</TBODY>

Maybe this will get you headed in the right direction. PivotTable will be much quicker and easier than a formula solution.