Sorting and Deleting Help
 

Good Day,

I have 3653 rows of data which have the following information by column;

ItemNumber Quantity Date
PRN223 56 02012003
PRN223 44 01042004
PRN223 41 06052003
PRN223 36 11272006
PRN223 49 08221999

What I need to do is first find the last time there was activity on the
item number, the example above would be the 11272006 date as the last
activity,
I then want to purge all the other rows except the one record. I have 562
different item numbers total.

Sorting and Deleting Help
 

You don't specify your version
If you use Excel 2007:
1. You will first have to convert the date-like numbers to actual dates
Assuming your dates are in column C, paste this in column D:
=DATE(RIGHT(C1,4),LEFT(C1,2),MID(C1,2,2))
2. Sort dates Descending, so latest date in on top
3. Remove duplicates Key:= item number

If you don't use 2007:
3. Filter for unique records
4. Copy to a new location

"LOU" wrote:

Good Day,

I have 3653 rows of data which have the following information by column;

ItemNumber Quantity Date
PRN223 56 02012003
PRN223 44 01042004
PRN223 41 06052003
PRN223 36 11272006
PRN223 49 08221999

What I need to do is first find the last time there was activity on the
item number, the example above would be the 11272006 date as the last
activity,
I then want to purge all the other rows except the one record. I have 562
different item numbers total.

Sorting and Deleting Help
 

Thank you for the information. I am not sure how to filter it based on the
unique record, the date is in a good date format and I have sorted it by the
item number and date descending. How do I filter for only unique records?

Thank you.
Lou

"Tevuna" wrote:

You don't specify your version
If you use Excel 2007:
1. You will first have to convert the date-like numbers to actual dates
Assuming your dates are in column C, paste this in column D:
=DATE(RIGHT(C1,4),LEFT(C1,2),MID(C1,2,2))
2. Sort dates Descending, so latest date in on top
3. Remove duplicates Key:= item number

If you don't use 2007:
3. Filter for unique records
4. Copy to a new location

"LOU" wrote:

Good Day,

I have 3653 rows of data which have the following information by column;

ItemNumber Quantity Date
PRN223 56 02012003
PRN223 44 01042004
PRN223 41 06052003
PRN223 36 11272006
PRN223 49 08221999

What I need to do is first find the last time there was activity on the
item number, the example above would be the 11272006 date as the last
activity,
I then want to purge all the other rows except the one record. I have 562
different item numbers total.