Data Filtering

[JTullah]

I have a list of data in Excel. It is a list of Transactions for Inventory records. For example

Item Description Date Amount
1234, Test Item, 01/01/06, 100.00
1234, Test Item, 01/06/06, 150.00
5426, AN other item, 05/05/06, 170.00
5426, AN other item, 12/12/06, 100.00

I want to only display one line for each item, showing the latest transaction amount, based upon the most recent transaction date. i.e. using the example above the list would show;

Item Description Date Amount
1234, Test Item, 01/06/06, 150.00
5426, AN other item, 12/12/06, 100.00

I am using MS Query to retireve the data from a SQL2000 source. Ideally i would like to filter the data at retieval stage, however if i can not i am happy for excel to filter the list.

Help!

 

[Tyarra]

I'm sure a MSQuery person will post how to filter at the time of query, but if not, try this:

Sort your data, by Item (ascending) and by Date (Decending)

In the column following your last bit of data, in the first row of data (assuming E2 here) enter, then copy down:
=IF(A2<>A1,"TRUE", "FALSE")

Then, on Column E do an Autofilter, for "TRUE", this will be your last date for each item.

 

[JTullah]

Thank you Tyarra, this works well!