+ Reply to Thread
Results 1 to 8 of 8

removing unwanted duplicate lines

  1. #1
    Registered User
    Join Date
    04-13-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    48

    removing unwanted duplicate lines

    Hi, I have a report that has multiple lines for the same job with notes of issues and solutions. Each line has a date assosiated with it.

    What I need to do is to delete all of the notes apart from the first one. I have attached a sample of the data and highlighted the rows that need to be kept.

    I have left the data in it's raw form as it is not always extracted in date order.

    Can anyone help me out with a Macro that will sort the data and remove all rows apart from the earliest note per job number?

    thanks
    Attached Files Attached Files
    Last edited by dperry; 09-22-2009 at 12:13 AM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: removing unwanted duplicate lines

    Hello dperry,

    How nice is it the land of sails today? Here ia macro to sort the comments by date and deletes all those after the earliest date. his macro has been added to the attached workbook.
    Please Login or Register  to view this content.
    To Run the Macro...
    To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    04-13-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    48

    Re: removing unwanted duplicate lines

    Hi Leith

    It's always great down here! Weather not neccessarily though

    Thanks for the macro, it worked perfectly on the sample data, but when I ran the full extract it ran into an error. In debug mode it highlighted the follwing code as being a problem

    DelRng.Value = Codes
    So what happened is that the data in column A was deleted for all of the job records after the earliest date, but the rest of the data in the rows were still there. BTW the full report has (at the moment) 23170 rows.

    thanks Daniel

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: removing unwanted duplicate lines

    Hello Daniel,

    Besides the number of rows, are there any other differences in actual data compared to the sample data?

  5. #5
    Registered User
    Join Date
    04-13-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    48

    Re: removing unwanted duplicate lines

    Hi Leith

    No, the only other difference would be the number of lines per job which is variable

    thanks

    Daniel

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: removing unwanted duplicate lines

    Hello Daniel,

    I am still researching this problem. I believe the problem is related to memory size of the array. I will modify the macro to use smaller arrays rather than one large array (codes).

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: removing unwanted duplicate lines

    Hello Daniel,

    I revised the macro to break down the data into 4K blocks. Run this on a copy of the original data first. It seems to work fine in my tests, but the real test is using the raw data.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-13-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    48

    Re: removing unwanted duplicate lines

    Hey Leith

    Sorry for the delay in responding, only just been able to play with this again.

    The new code worked like a charm! 24000 lines sorted and unrequired lines deleted with no problems at all.

    Many thanks for all of your help

    Daniel

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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