Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Delete Duplicate Rows (ADVANCED)

  1. #1
    Join Date
    17th August 2004
    Location
    Kentucky, USA
    Posts
    14

    Delete Duplicate Rows (ADVANCED)

    Good Morning All,

    I did a quick search for this and was able to find several items concening deleting duplicate rows, however I need to extend this VBA script to do some more detailed work. I am able to read scripts fairly well, but writing them is coming a bit slower.

    What I need is a script that will delete duplicate rows based on several variables. The first being that the row is duplicate in more than one cell, and the second that the rows are only duplicate on the same day. I will attach an example of what I am working with to this post to help explain my need. Any help in this project would be greatly appreciated. My biggest worry is that any script I use will inadvertently delete rows that I don't consider duplicate.. since this is to be used at work that wouldn't be a good thing =)

    The data in 5 different columns is what will be pertinent to the duplicate rows, I've put an explanation on the excel spreadsheet to help show what I am looking for. In my example only the shaded rows should be deleted because they are exactly the same and on the same date.

    Hopefully this all makes sense, if anyone wants clarification please let me know! Thanks greatly for any help you can give me!

    Edwa5823
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th May 2004
    Location
    London
    Posts
    447
    OK I think I follow you - I've only sorted by 3 columns but this should do what you want.

    I get the impression this is only a small sample so I have put in something that will calculation the %complete of rows being processed in the status bar so you can tell it's working etc...

    Have called the sheet containing the data "SAMPLE" so change this in the code as required.

    Also I switched calculation to manual to try and speed it up - if any of the values in the data are the results of formulae (ie not just values in the cells) then post back and let us know where.

    Anyway - hope this gets you started.

    (Insert as module)


    VB:
    Sub DELETE_DUPES() 
         
        Dim str As String 
        Dim str2 As String 
        Dim c As Integer 
        Dim i As Integer 
         
    On Error Goto Handler: 
         
        Application.ScreenUpdating = False 
        Application.Calculation = xlCalculationManual 
         
        Sheets("SAMPLE").Select 
         
        rw = Cells(2, 1).End(xlDown).Row 
         
         'Sort Data by Date, Location & Number
        Range(Cells(1, 1), Cells(1, 7)).Select 
        Range(Selection, Selection.End(xlDown)).Select 
        Selection.Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2) _ 
        , Order2:=xlAscending, Key3:=Cells(1, 3), Order3:=xlAscending, Header:= _ 
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ 
        xlSortNormal 
         
         'i = 2nd row of data
        i = 3 
        Do Until i > rw Or Cells(i, 1) = "" 
            For c = 1 To 7 
                If Cells(i, c) <> Cells(i - 1, c) Then Goto 10 
            Next c 
            Rows(i & ":" & i).Delete 
            rw = rw - 1 
            Goto 20 
            10 
            i = i + 1 
            20 
            Application.ScreenUpdating = True 
            Application.StatusBar = Format((i / rw) * 100, "##0.00") & "% Complete" 
            Application.ScreenUpdating = False 
        Loop 
         
        Application.ScreenUpdating = True 
        Application.Calculation = xlCalculationAutomatic 
        Application.StatusBar = False 
         
        Exit Sub 
         
    Handler: 
        Application.ScreenUpdating = True 
        Stop 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    14th August 2004
    Location
    San Francisco, CA
    Posts
    9
    Does that solve your problem? It seems to work and should not be difficult to adapt. The procedure is not optimized but should be good enough for small datasets...

    Cheers,

    VB:
    Sub deleteDuplicate(WSName As String) 
         
        Dim cRow As Integer 
        Dim cRow2 As Integer 
         
        Dim cCol As Integer 
        Dim foundDuplicate As Boolean 
         
        cRow = 2 
        Do While IsEmpty(Worksheets(WSName).Cells(cRow, 1)) = False 
            cRow2 = cRow + 1 
            Do While IsEmpty(Worksheets(WSName).Cells(cRow2, 1)) = False 
                foundDuplicate = True 
                For cCol = 1 To 7 
                    If Worksheets(WSName).Cells(cRow, cCol).Value <> Worksheets(WSName).Cells(cRow2, cCol).Value Then 
                        foundDuplicate = False 
                        Exit For 
                    End If 
                Next 
                If foundDuplicate = True Then 
                    Worksheets(WSName).Rows(cRow2).Delete xlShiftUp 
                Else 
                    cRow2 = cRow2 + 1 
                End If 
            Loop 
            cRow = cRow + 1 
        Loop 
         
    End Sub 
    Sub test() 
        deleteDuplicate "Sheet1" 
    End Sub 
    
    
    Mathias Brandewinder
    Clear Lines Consulting

  4. #4
    Join Date
    17th August 2004
    Location
    Kentucky, USA
    Posts
    14
    Well, I *Thought* it was an advanced topic.. LOL Thanks for the help.. I had a little bit of problems with the first bit of code there LASW10.. I had to define the variable rm and it kept wanting to debug on the STOP command in the error handler. I appreciate the effort though!

    Mathias, yours seemed to do the trick for me, It only catches what I consider to be duplicate rows and leaves the rest in my database..

    Once again thanks for all of the help! It will be put to good use.

    Edwa5823

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th August 2004
    Location
    Kentucky, USA
    Posts
    14
    Hello, it's me again.. I'm going to "Necro-post" on this thread because I realized that I have a revision I need to make to the code that was provided, not sure how to do it though.

    The code below does exactly what I asked for it to do.. I did forget to mention one thing in my original question however.. The 7th column of my data is a "Comments" section. Since other people supply me with this information the comments column tends to be different every time. The delete duplicate rows is checking this column as well, and I need to find a way to have it check everything BUT column 7. Is it possible to modify this code to do what I need?

    Thanks in advance again for your assistance!



    VB:
    Sub deleteDuplicate(WSName As String) 
         
        Dim cRow As Integer 
        Dim cRow2 As Integer 
         
        Dim cCol As Integer 
        Dim foundDuplicate As Boolean 
         
        cRow = 2 
        Do While IsEmpty(Worksheets(WSName).Cells(cRow, 1)) = False 
            cRow2 = cRow + 1 
            Do While IsEmpty(Worksheets(WSName).Cells(cRow2, 1)) = False 
                foundDuplicate = True 
                For cCol = 1 To 7 
                    If Worksheets(WSName).Cells(cRow, cCol).Value <> Worksheets(WSName).Cells(cRow2, cCol).Value Then 
                        foundDuplicate = False 
                        Exit For 
                    End If 
                Next 
                If foundDuplicate = True Then 
                    Worksheets(WSName).Rows(cRow2).Delete xlShiftUp 
                Else 
                    cRow2 = cRow2 + 1 
                End If 
            Loop 
            cRow = cRow + 1 
        Loop 
         
    End Sub 
    Sub test() 
        deleteDuplicate "Sheet1" 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th August 2004
    Location
    San Francisco, CA
    Posts
    9
    Replace
    VB:
    For cCol = 1 To 7 
    
    
    by
    VB:
    For cCol = 1 To 6 
    
    
    that should do the job!

    Cheers,
    Mathias Brandewinder
    Clear Lines Consulting

  7. #7
    Join Date
    17th August 2004
    Location
    Kentucky, USA
    Posts
    14
    Easy enough, thanks again, and thanks even more for the quick response! It doesn't take long before I start trying it myself and things usually go down hill from there. =)

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,977
    Not trying to knock the information above but:

    A quick question. When you perform the code will there be alot of deletions? The reason why I ask is that your code will execute faster if you can do a clearcontents and then sort the table of data.


    I've found that if I'm deleting 1000's of lines that it is really really slow. It gets even slower if there are formulas on the affected sheet. By doing a .clearcontents instead of .delete it is much much faster.

    If your deleting a small number than the examples given above are fine.

    FYI.
    Last edited by iwrk4dedpr; August 18th, 2004 at 11:57. Reason: More info

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    14th August 2004
    Location
    San Francisco, CA
    Posts
    9
    Thanks for the info - I'll definitely check and compare, out of curiosity! This being said, I like deletion because it keeps the list in one block, as opposed to creating rows with no data. This way, you don't have to worry about empty cells

    Cheers,
    Mathias Brandewinder
    Clear Lines Consulting

  10. #10
    Join Date
    17th August 2004
    Location
    Kentucky, USA
    Posts
    14
    I won't have any forumlas in the sheet (This list is actually feeding an access database) but it will end up having a LOT of rows. Unfortunatly I do need to keep block integrity as much as possible. The run time shouldn't be too much of a problem though, the main file that this supports has over 2500 arrays and several hundered formulas.. Takes about 7 minutes to process =P

    Thanks for the help and advise!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Consolidate Matching Rows And Delete Duplicate Rows
    By Charltp5 in forum EXCEL HELP
    Replies: 4
    Last Post: July 6th, 2013, 12:42
  2. Delete Duplicate Rows
    By praveen_khm in forum EXCEL HELP
    Replies: 7
    Last Post: June 4th, 2008, 19:50
  3. Delete Duplicate Rows
    By nester in forum EXCEL HELP
    Replies: 5
    Last Post: April 9th, 2007, 23:44
  4. Delete duplicate rows
    By prayad in forum EXCEL HELP
    Replies: 6
    Last Post: February 26th, 2003, 03:35

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
  •  
porno