Grouping and Levels

[8cats]

I have a spreadsheet that is sorted by an item number and the date. I need to only show or extract the most recent date for that particular item number. It would have been simple, but there is one field that I need that is different for each record.

Example:

Item # Date Item Name PO # PO Rel #

12345 11/01/06 clamp 22222 8
12345 10/25/06 clamp 22222 5
12345 10/10/06 clamp 22222 7

12366 11/08/06 syringe 34567 5
12366 11/02/06 syringe 34567 8


By this example I already have it sorted by date and item #. I only want to show the last date for item 12345 and then the last date for item 12366, which would be the first record for each etc. I tried subtotals and then grouping and levels, but I cannot isolate the record that I need for each group.

Any help is appreciated.

Thank you.

[Bryan Hessey]

I have a spreadsheet that is sorted by an item number and the date. I need to only show or extract the most recent date for that particular item number. It would have been simple, but there is one field that I need that is different for each record.

Example:

Item # Date Item Name PO # PO Rel #

12345 11/01/06 clamp 22222 8
12345 10/25/06 clamp 22222 5
12345 10/10/06 clamp 22222 7

12366 11/08/06 syringe 34567 5
12366 11/02/06 syringe 34567 8


By this example I already have it sorted by date and item #. I only want to show the last date for item 12345 and then the last date for item 12366, which would be the first record for each etc. I tried subtotals and then grouping and levels, but I cannot isolate the record that I need for each group.

Any help is appreciated.

Thank you.

I presume that by 'last date' you mean the first date listed, the most recent date, then the easy way would be a helper column (say H) and in h1 put 1, in h2 put

=If(A2=A1,2,1)

and formula fill that to the end of your data, then Data Filter, auto filter, and on the helper column select 1

If you want the oldest date (the last listed) the formula

=If(A2=A3,2,1)

would be the way.

Hope this helps.
---