none
Compare Two Spreadsheets and Delete Row IF RRS feed

  • Question

  • I have two spreadsheets, they are almost identical except for one column.

    I want to be able to search Column A of sheet1 for a match in Column A of sheet2.

    If there is a match, I would like to search column F for a specific word.

    If the word is present, I would like to delete the matching row on Sheet1.

    Any ideas?

    
    Friday, June 29, 2012 6:37 PM

All replies

  • 1. Do you mean that you want to test every cell in column A of Sheet1 to see if there is a match in Column A of Sheet2?

    2. Then for each match between Sheet1 and Sheet2, search for a specific word in column F (of Sheet2 I think; please confirm) and if it exists, delete the entire row in Sheet1.

    3. The word to search for in column F. Is it always the same word and if not, where does it come from.

    4. Also, does the word in column F have to be on the same row as where the match was found or just anywhere in the column.


    Regards, OssieMac

    Saturday, June 30, 2012 6:07 AM
  • Thanks for the reply.

    1.Yes that is exactly what I mean.

    2.Correct Column F of sheet2.

    3. It will always be the same word.

    4.The word in column F have to be on the same rwo as where the match was found.

    Saturday, June 30, 2012 10:51 AM
  • Hello again lilmissfield, (I like the pseudonym)

    It is a good idea to ensure that you have a backup of any workbook where you are running code to delete data; especially during testing. You cannot undo the deletes like when manually deleting data.

    Try the code below. You will see the code between the asterisk lines is so that you can run the code and ensure that it will delete the correct lines. It simply sets the interior color of the rows to yellow without deleting them. When you are satisfied the the code is identifyng the correct lines then comment out the line to set the color and uncomment the next line to delete the rows.

    Sub DeleteRowsIfMatch()
        Dim rngToSearch As Range
        Dim rngToFind
        Dim lngLastRow
        Dim r As Long
        Dim strWordToFind As String
        Dim strFirstAddr As String
       
        strWordToFind = "MyTest"    'Edit "MyTest" to the correct word to search for in column F.
       
        With Sheets("Sheet2")       'Edit "Sheet2" to the sheet to be searched
            'Following line assumes comumn header in row 1 and starts at row 2
            Set rngToSearch = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
        End With
       
        With Sheets("Sheet1")       'Edit "Sheet1" to the sheet to have rows deleted
            lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
       
            'Need to work backwards when deleting rows
            For r = lngLastRow To 2 Step -1     'To 2 omits column headers in row 1
           
                Set rngToFind = rngToSearch.Find(What:=.Cells(r, "A"), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                               
                If Not rngToFind Is Nothing Then
                    strFirstAddr = rngToFind.Address
                    'Do loop incorporated in case more than one match in Sheet2 and _
                     the word in column F is not found on the first find match.
                    Do
                        If LCase(rngToFind.Offset(0, 5)) = LCase(strWordToFind) Then
                            '*************************************************************************
                            .Rows(r).Interior.Color = vbYellow  'Use in lieu of next line for testing
                            '.Rows(r).Delete
                            '*************************************************************************
                            Exit Do     'Can only delete row once so need to exit if match found
                        End If
                        Set rngToFind = rngToSearch.FindNext(rngToFind)
                        If rngToFind Is Nothing Then Exit Do
                    Loop While rngToFind.Address <> strFirstAddr
                End If
            Next r
        End With

    End Sub


    Regards, OssieMac

    Sunday, July 1, 2012 2:14 AM