+ Reply to Thread
Results 1 to 17 of 17

Delete Duplicate records but keep 1 record with most recent date

  1. #1
    Forum Contributor
    Join Date
    08-17-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    158

    Delete Duplicate records but keep 1 record with most recent date

    Hello!

    I have a table that contains data from columns A:H.

    Column B is a Date Field (Short Date Format= mm/dd/yyyy)
    Column C is an alpha numeric field (Text Format)

    There can be duplicate values in column C, but each record will have a different date. For Example, let's say I have the following records:

    Row # Column B Column C

    1 03/25/2013 11030
    2 05/07/2013 11030
    3 07/01/2013 11030

    In this example, I want to only keep Row #3 as this record has the most recent date. The number "11030" has been duplicated 3 times. So my criteria for deleting duplicates is to keep the duplicated record which has the most recent date and this code needs to cycle through the data set to find the duplicates and ONLY keep the most recent date. Something to keep in mind while developing this code: I have seen 2-6 duplicate records. Please help! I do not want to have to do this manually by hand as there's thousands of records to go through! Any help will be greatly appreciated.

  2. #2
    Forum Contributor
    Join Date
    06-26-2013
    Location
    Makati
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: Delete Duplicate records but keep 1 record with most recent date

    Hi,

    Did you try to use Remove duplicate function?

    you can try to use this step
    - sort the whole data by the date from Z-A ( sort newest to oldest )
    - Block all the data and klik remove duplicates
    - Uncheck all the tick box and only check the column that contain duplicates

    It will keep the unique value with only recent dates.

  3. #3
    Registered User
    Join Date
    12-11-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Delete Duplicate records but keep 1 record with most recent date

    I just tried this suggestion and the Remove Duplicates Function does keep the Unique Value BUT NOT WITH THE RECENT DATES.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Delete Duplicate records but keep 1 record with most recent date

    Hi,

    Try sorting your data first by date with the newest to oldest option set. Then use the remove duplicates functionality.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    12-11-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Delete Duplicate records but keep 1 record with most recent date

    Hi, the options I have on the sort is A-Z. So the oldest is the first record and the newest date is the last record.

  6. #6
    Registered User
    Join Date
    12-11-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Delete Duplicate records but keep 1 record with most recent date

    Here is the data. The yellow rows are the most current "status" date. I want to keep those rows and delete the older dates.

    Capture.PNG

  7. #7
    Registered User
    Join Date
    10-21-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Delete Duplicate records but keep 1 record with most recent date

    I would do a sort on the values.
    Then modify this simple VBA code to fit your needs.
    Please Login or Register  to view this content.
    Last edited by stibay; 01-08-2014 at 02:27 PM.

  8. #8
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Delete Duplicate records but keep 1 record with most recent date

    Bevg, try this "cheat". Sort your data like this: First level = Column C A-Z. Second level = Column B newest to oldest. Now, based on your sample data above, in cell D2 put formula "=IF(D2<>D1,1,0)". Now, just filter for the 1's.

  9. #9
    Registered User
    Join Date
    12-11-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Delete Duplicate records but keep 1 record with most recent date

    Hi,

    GSNIDOW, Thanks for the "cheat". I love any short cut but this one only gave me one of my yellow rows and I need all three after the "Remove duplicates" function.

    attached is a file so that you can try it out for yourself.


    Puzzle.xls

  10. #10
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Delete Duplicate records but keep 1 record with most recent date

    Try this. In cell E2, put formula "=IF(A2=A1,E1+1,1)" and copy down to end of range. This is after sorting by A ascending, and D descending. Without doing anything with the remove dupes button, you can filter column E for "1", and these are the ones you want to keep. Delete everything where E<>1.

    Greg
    Just a guy trying to make work stuff easier.

  11. #11
    Registered User
    Join Date
    12-11-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Delete Duplicate records but keep 1 record with most recent date

    Hi stibay,

    Thanks a tons for the code! I'm having difficulty with it. I changed the code so that it compares the rows in column A and compares the rows in column C "if matches" to previous row then

    Compare the "Status Date" if if date is greater than previous date delete the previous date (this will keep the most current date).

    attached is the file. I did sort the data code=level 1, name level 2, rcvd date=level 3, status date=level 4

    Puzzle.xls

  12. #12
    Registered User
    Join Date
    12-11-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Delete Duplicate records but keep 1 record with most recent date

    I love your tag line "Just a guy trying to make work stuff easier"

    Hey I tried your new suggestion and a "1" did not appear on my yellow rows. bummed.

    I did sort the data code=level 1, name level 2, rcvd date=level 3, status date=level 4

    The column Status Date is the most recent date I need to keep.

  13. #13
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Delete Duplicate records but keep 1 record with most recent date

    Ok, I think the issue is with the sorting. I took Puzzle.xls spreadsheet, and I added column "E", with header "Status Date2" and formated as date. In E2, populate formula "=DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,3,2))", then drag down. Now, to sort, level 1 is Code, ascending. Level 2 is Name ascending. Level 3 is our new "Status Date2" newest to oldest. Don't do anything with received date. Now, in cell F2 put this formula "=IF(AND(A2=A1,C2=C1),F1+1,1)" and drag down. On your spreadsheet, it populates a "1" on the rows you have highlighted. This brings up an important issue for you: always be aware of your data types, and of how they behave in calculations.

    Greg

  14. #14
    Registered User
    Join Date
    12-11-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Delete Duplicate records but keep 1 record with most recent date

    AWESOME GREG! It works!

    Thanks for the heads up on the data types. The system I'm migrating data into is requiring all dates to be MMDDYYYY this format is not working with access nor excel.

    This was a quick short cut as well. I will definitely use this method again.

    Looks like you won the competition and now are crowned King of the spreadsheet!!! Have a good day!

  15. #15
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Delete Duplicate records but keep 1 record with most recent date

    I'm glad I could help. I'm a SQL guy mostly, so I think of lots of stuff in terms of SQL. The solution I offered you is a round-about way of making SQL"s ROW_NUMBER() function. Quite handy in lot's of situations.

    Greg

  16. #16
    Registered User
    Join Date
    09-08-2015
    Location
    London
    MS-Off Ver
    10
    Posts
    1

    Re: Delete Duplicate records but keep 1 record with most recent date

    Hi All
    I am facing problem the last row function is not calculating as such
    you can see the codes below

    ActiveSheet.Range("A3:A" & lastRow).RemoveDuplicates Columns:=1, Header:=xlYes
    Sheets("Daily PSA").Select
    Dim last As Long
    With Sheets("Fill The Data")
    last = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    Range("B4:B" & last).FormulaR1C1 = "=COUNTIFS('Fill The Data'!C19,""HIT"",'Fill The Data'!C3,'Daily PSA'!RC[-1])/COUNTIF('Fill The Data'!C3,'Daily PSA'!RC[-1])"

    The last formula is pasted in all the cells which were present in Col A before removing the duplicates. Please correct the code if possible

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,316

    Re: Delete Duplicate records but keep 1 record with most recent date

    Tushar, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. remove duplicate records based on most recent date
    By dajlwj in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 10-25-2013, 07:56 AM
  2. [SOLVED] Identify duplicate rows and delete all but the most recent
    By millerj64 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-10-2012, 03:06 PM
  3. Replies: 3
    Last Post: 12-31-2011, 10:19 PM
  4. Merge duplicate records in single record
    By pethaa1791 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-17-2008, 02:34 PM
  5. delete duplicate records after totaling a field from each record
    By HarrySpencer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2007, 09:49 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1