Calculating the last value per group

[lord_yo]

Hi everyone

I have a table which includes the names of people, a date field when something happened, and a value for that date. The list is not sorted and changed regularly.

ID, Name, Date, Value
01, Peter, 2013-05-12, 100
01, Peter, 2013-10-19, 104
02, Paul, 2013-12-01, 99
03, Jake, 2014-03-12, 54
01, Peter, 2014-03-05, 109
02, Paul, 2013-01-12, 78

What I'm looking for is a way to calculate/display a shortened list including only the latest entry by person.

ID, Name, Date, Value
02, Paul, 2013-12-01, 99
03, Jake, 2014-03-12, 54
01, Peter, 2014-03-05, 109

I can't change or sort the raw data itself (except adding additional calculated columns), and can't use VBA. Is there a way to make this work using pivot tables? E.g. an additional column entry displaying the value only if the date is the newest for this person?

Thanks for your help!

 

[Weazel]

I can't think of a better way to do it without creating a unique list of IDS...

=INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF($F$1:F1,$A$2:$A$7),0),0)) to get a unique list of ID's


then in G2

=INDEX(B$2:B$7,MATCH(MAX(IF($A$2:$A$7=$F2,$C$2:$C$7,0)),$C$2:$C$7,0)) Control Shift Enter


[TABLE="class: grid, width: 588"]
<TBODY>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[TD]h
[/TD]
[TD]i
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ID</SPAN>
[/TD]
[TD]Name</SPAN>
[/TD]
[TD]Date</SPAN>
[/TD]
[TD]Value</SPAN>
[/TD]
[TD][/TD]
[TD]ID</SPAN>
[/TD]
[TD]Name</SPAN>
[/TD]
[TD]Date</SPAN>
[/TD]
[TD]Value</SPAN>
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1</SPAN>
[/TD]
[TD]Peter</SPAN>
[/TD]
[TD="align: right"]5/12/2013 </SPAN>
[/TD]
[TD="align: right"]100</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD]Peter</SPAN>
[/TD]
[TD="align: right"]3/5/2014
[/TD]
[TD="align: right"]109</SPAN>
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1</SPAN>
[/TD]
[TD]Peter</SPAN>
[/TD]
[TD="align: right"]10/19/2013 </SPAN>
[/TD]
[TD="align: right"]104</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD]Paul</SPAN>
[/TD]
[TD="align: right"]12/1/2013
[/TD]
[TD="align: right"]99</SPAN>
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2</SPAN>
[/TD]
[TD]Paul</SPAN>
[/TD]
[TD="align: right"]12/1/2013 </SPAN>
[/TD]
[TD="align: right"]99</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD]Jake</SPAN>
[/TD]
[TD="align: right"]3/12/2014
[/TD]
[TD="align: right"]54</SPAN>
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3</SPAN>
[/TD]
[TD]Jake</SPAN>
[/TD]
[TD="align: right"]3/12/2014 </SPAN>
[/TD]
[TD="align: right"]54</SPAN>
[/TD]
[TD][/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1</SPAN>
[/TD]
[TD]Peter</SPAN>
[/TD]
[TD="align: right"]3/5/2014 </SPAN>
[/TD]
[TD="align: right"]109</SPAN>
[/TD]
[TD][/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2</SPAN>
[/TD]
[TD]Paul</SPAN>
[/TD]
[TD="align: right"]1/12/2013 </SPAN>
[/TD]
[TD="align: right"]78</SPAN>
[/TD]
[TD][/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

don't know if this is something you can work with based on your restrictions