Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing duplicate records based off of date
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing duplicate records based off of date
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 dont lose time getting back because you dont understand. (But feel free to get back if anything you dont 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 dont 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. -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing duplicate records based off of date
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 -- Regards, Ashish Mathur Microsoft Excel MVP "Jen_T" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing duplicate records based off of date
Excel 2007 PivotTable
No code, no formulas. http://c0718892.cdn.cloudfiles.racks.../03_31_10.xlsx Pdf preview: http://c0718892.cdn.cloudfiles.racks...m/03_31_10.pdf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing duplicate records with long names in Excel 2007 | Excel Discussion (Misc queries) | |||
Delete records when certain records have duplicate column data | New Users to Excel | |||
Removing all duplicate records except one copy | Excel Discussion (Misc queries) | |||
removing duplicate records in excel, how to do it? | Excel Discussion (Misc queries) | |||
In Excel, how do I get rid of duplicate records? Based on text r. | Excel Discussion (Misc queries) |