Removing duplicate records based off of date

[Jen_T]

Is there a way to look at a group of records where some have been duplicated
and for those that are duplicated , keep the most current date (keeping that
row of data)?
The records have a unique record number to identify if record is a
duplicate and I also have a record month/yr which would be different for the
duplicate records, please see example.. Note there are additional columns but
did not think necessary to list for the example.
I am also using Excel 2007

record # record month/yr
RT123 10/1/2009
RT123 11/1/2009
RT123 1/1/2010
MT122 10/1/2009
MM121 10/1/2009
NM119 1/1/2010

Final Result I am looking for:

record # record month/yr

RT123 1/1/2010
MT122 10/1/2009
MM121 10/1/2009
NM119 1/1/2010

Thank you in advance for any assistance.

 

[OssieMac]

Hello Jen,

I hope that I can help you. It is a long winded explanation but in reality
it does not take very long to complete once you know what to do.

I am not sure how much help you need with this so I will provide all
instructions so hopefully you don’t lose time getting back because you don’t
understand. (But feel free to get back if anything you don’t understand)

Ensure you have a backup of your workbook before commencing this.

Use Advanced Filter to create unique list of record numbers as follows.
Select the record numbers including the column header.
Select Advanced Filter. (See Help for more info in Advanced Filter)
Check Copy to another location.
Check Unique records only.
Click icon at right of Copy to field.
Select a cell directly below the bottom of the record number data in the
same column (that is column A)
Click the icon at right of displayed field to get back to main dialog box.
Click OK.

Select all of the original data including column headers (but don’t include
the unique data) and sort on the date in descending order (Most recent date
to top). (Ensure that you check box that your data has column headers.)

Go back down to your new unique list of record numbers.
In the cell in column B adjacent to first record number enter the the
Vlookup formula. (Note that I have used column(B:B) as the column index
number. This is so it becomes relative for the remaining columns in your data)

=VLOOKUP($A20,$A$2:$D$7,COLUMN(B:B),FALSE)

You will need to edit your row number for the lookup value and table array
will be your entire table of original data excluding the column headers. Note
that the lookup value $A20 has the preceding $ sign before the column but NOT
before the row. The table array is totally absolute with preceding $ signs
before the columns and rows. COLUMN(B:B) actually returns the column number
which for column B is 2.

You should be able to copy the formula to the bottom of the unique list of
record numbers and across to the last column of your other data.

Vlookup uses the first value that it finds so with the dates sorted in
descending order, it will be the most recent date.

Before deleting your original data ensure that the new data is correct.

Then select all of the new data and Copy -> Paste Special -> Values before
you delete the original data.

 

[Ashish Mathur]

Hi,

Try this.

1. Assume your data is in range B3:C9 (headings are in row3)
2. In cell C11, enter condition
3. In cell C12, enter the following formula
=C4=MAX(INDEX(($B$4:$B$9=B4)*($C$4:$C$9),,))
4. Click on any blank cell and go to Data > Filter > Advanced Filter
5. Select copy to another location and in the list range, select B3:C9
6. In the criteria range, select C11:C12
7. In the copy to box, select any blank cell and click on OK

You should get the desired output

 

[Herbert Seidenberg]

Excel 2007 PivotTable
No code, no formulas.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_31_10.xlsx
Pdf preview:
http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_31_10.pdf