Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you to all who replied. I think I may have left out part of the equation. I am editing my question.

    You have two (2) workbooks. Both workbooks have some of the same data in multiple columns in rows. Using the same column in each workbook, if there are duplicates from each workbook, delete the duplicate data in the entire row in the second workbook.

    Thanks again!

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 987 Times in 917 Posts
    That's a huge task IMHO. I wrote a spreadsheet to remove duplicates and compare 2 lists. I have attached it for you to peruse.

    cheers, Paul
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The following requires both workbooks to be open. It checks the A column in Book2 and if it finds a matching entry in the A column of Book1, it deletes the cell - can be made to just clear the cell instead.

    Code:
    Sub deleteDupes2Books()
    Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim rKeep As Range, lRow As Long, i As Long
    Set wb1 = Workbooks("Book1.xls")
    Set wb2 = Workbooks("Book2.xls")
    Set ws1 = wb1.Sheets(1)
    Set ws2 = wb2.Sheets(1)
    Set rKeep = ws1.Range(ws1.Cells(2, 1), ws1.Cells(Rows.Count, 1).End(xlUp))
    'MsgBox ws1.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)).Address
    'Exit Sub
    lRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    'MsgBox lRow
    On Error Resume Next
    
    For i = lRow To 2 Step -1
     
        
        If Application.WorksheetFunction.Match(ws2.Cells(i, 1), rKeep, 0) > 0 Then
     If Err.Number <> 1004 Then
            ws2.Cells(i, 1).Delete shift:=xlUp ' comment out this line and remove the comment the next if you just want to clear the contents of the cell
            'ws2.Cells(i, 1).ClearContent
        End If
        Err.Clear
    End If
    Next

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Please see this post for the continuation of the thread.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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