Most Recent Data in a Spreadsheet

T

Tom

I have a spreadsheet with multiple dates for multiple products and
prices in addition to other data in several colums. I would like to
pick the latest ie. most recent price for each product and also display
the additional data ie. all the data in the row.


01/01/2003 30000051 3.52 2,561 10,054
01/24/2004 30000051 3.65 3,465 5,678
04/07/2005 30000051 3.45 5,675 3,442
04/06/2004 30000077 4.55 6,994 3,997
05/22/2004 30000077 6.73 9,956 3,455
06/09/2005 30000077 9.56 5,812 4,778
08/08/2005 30000077 8.99 6,982 3,337
etc.

the end result should be the following data being displayed:

04/07/2005 30000051 3.45 5,675 3,442
08/08/2005 30000077 8.99 6,982 3,337

Thanks
 
M

mrice

A neat way to do this would be to add an additional column containing
for formula..

=EXACT(B2,B3) in column 2 and copy down.

This will show when a cell in column B is different to the one below
it.

Assuming that you have sorted as shown, the lines which have FALSE
against them should be the most recent and a simple auto filter will
show these.

The attachment shows how.


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4685 |
+-------------------------------------------------------------------+
 
T

Tom

This will almost work, one addendum to the information:

01/01/2003 30000051 3.52 2,561 10,054
01/24/2004 30000051 3.65 3,465 5,678
04/07/2005 30000051 3.45 0 3,442
04/06/2004 30000077 4.55 6,994 3,997
05/22/2004 30000077 6.73 9,956 3,455
06/09/2005 30000077 9.56 5,812 4,778
08/08/2005 30000077 8.99 6,982 3,337


How can I modify your formula if with the most recent information ie in
column 4 there is a zero, this line should be FALSE and the line above
should be TRUE, for the other rows the result would be OK as per your
formula.

Thanks
 
Top