How to delete rows based on comparing values in two fields

[OmniBlue]

I have an Excel sheet with ID numbers, Dates, and other columns as shown below. I would to remove rows such that I'm left with only one row for each ID number, and it would be the row with the most current date. Can anyone please tell me how this can be done? Thank you!

ID Date Other columns
1 5/20/13
1 12/27/12
2 2/5/11
3 9/4/12

[jeffreybrown]

Here are a couple of options, but they don't actual delete any of the real data in case you actually need it later on.

[OmniBlue]

Thank you, Jeff. Your pivot table shows the ID and Dates exactly exactly as I had intended. However, for each of those three rows, I would like the table to include data from the other columns of my original spreadsheet, as well. When I attempt to add those, there are problems with the layout. I'm rather new to Excel pivot tables, as well!

[jeffreybrown]

If you could provide a mock up of what you have and what you desire that would help.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[OmniBlue]

I was able to solve this by sorting (by ID and then by Date); then comparing values in the ID field using an If statement and populating a "flag" field as either 1 or 0 (If the ID number in row 2 = the ID number in row 1, flag field = 0, else flag field =1),and the deleting all rows with a flag field value of 0. The result is one row for each ID number (the row with the most recent date), and all other data columns in the spreadsheet hung on for the ride.

Thank for your help, Jeff. I wasn't aware that I could attach a file to clarify my question--thanks for passing that along!

[jeffreybrown]

You're welcome…glad you have a workable solution and thanks for the feedback...