+ Reply to Thread
Results 1 to 4 of 4

Deleting all but one record from the same day

  1. #1
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Deleting all but one record from the same day

    I've got a dataset comprising 200k rows. The columns are Author and Date. Many authors have more than one record for the same day, differing only in the time recorded for that day.

    I want to prune this dataset so that an Author has only one record for each day. Is there an easy way of doing this?

    Sample data attached.

    Thank you.

    Sample Data.xlsx
    Last edited by Debatewise; 07-27-2013 at 10:39 AM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,053

    Re: Deleting all but one record from the same day

    Maybe a pivot table with an added column to extract just the date!
    Attached Files Attached Files
    HTH
    Regards, Jeff

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Deleting all but one record from the same day

    In C2 Fill Down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In D2 Fill Down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Filter Column D for blanks

    Delete the resulting rows

    Remove the filter

    Delete Columns C&D
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Deleting all but one record from the same day

    Thanks Marcol, you've solved another problem for me

    I had a bit of a job with removing the unwanted records. I wasn't sure about filtering for blanks so ended up using column E to check column D for '1' and entering "Delete" if it didn't appear. I then replaced column C with the contents of E, copied cols A, B and C to a new workbook, replaced "Delete" with nothing, sorted on C and deleted all the rows where C was empty.

    I had to go through this process because it seemed the contents of D were really slowing the workbook down. I've got a pretty decent spec (Core i5, 8Gb Ram, SSD) but a sort I started at 8pm last night hadn't finished by the time I went to bed. And when I woke up, the sort said it had finished but nothing on the workbook had changed . Even trying to delete the contents of column D took over an hour with no discernible progress.

    Anyway, I've got the results I wanted. Thank you.

+ 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. Deleting record in rows
    By eexpert in forum Excel General
    Replies: 4
    Last Post: 06-16-2013, 06:01 AM
  2. [SOLVED] Deleting a record in a table, using VBA
    By nav505 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-13-2013, 02:39 PM
  3. [SOLVED] Deleting Rows after Last Record using VBA
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-27-2012, 05:06 AM
  4. Deleting a Record a Worksheet
    By jason_kelly in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2010, 06:34 PM
  5. Copy a record before deleting
    By Shinu in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-15-2006, 05:10 AM

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