if a cell doesnt match another cell in a different sheet remove the original cell in excel

Posted on 2014-08-02
Last Modified: 2014-08-03
In the same workbook I have a column (colA) in sheet1 and a column in sheet2 (colA) and the same in sheet 3&4. There is other info in the other columns in each sheet

In the column are reference numbers. I would like to make sure that the same number is in all the sheets. If it isnt, then the entire row should be removed for the sheet that the match is being done from. I will of course be running this on all the sheets so I will eventually end up with the same numbers in the same rows.

As an example:

Sheet 1                     Sheet 2                        Sheet 3
leftPos            JWA          leftPos             JWB           leftPos      JWC
100260304      96      100260304      62      100202137      1
100735392      129      100724039      1      100260304      75
100805662      122      100735392      175      100735392      306

So the following should be removed

Sheet 1 100805662 122
Sheet 2 100724039 1
Sheet 3 100202137      1
Question by:sebastizz
    LVL 20

    Expert Comment

    by:Ejgil Hedegaard
    Try this, run the macro in module 1 in attached file.

    Option Explicit
    Sub RemoveItemsNotOnAllSheets()
        Dim ws As Worksheet, wsCompare As Worksheet, wsNow As Worksheet
        Dim rw As Long, rwStart As Long, rwEnd As Long, i As Integer
        Dim SheetName As String, nbrSheets As Integer
        Application.ScreenUpdating = False
        Set wsNow = ActiveSheet
        For Each ws In Worksheets
            If ws.Name = "CompareSheet" Then
                i = 1
                Set wsCompare = ws
            End If
        Next ws
        If i = 0 Then
            Set wsCompare = Worksheets.Add
            wsCompare.Name = "CompareSheet"
        End If
        wsCompare.Cells(1, 1) = "ID"
        wsCompare.Cells(1, 2) = "SheetName"
        wsCompare.Cells(1, 3) = "Row"
        wsCompare.Cells(1, 4) = "OnSheets"
        rwEnd = 1
        For Each ws In Worksheets
            If ws.Name <> "CompareSheet" Then
                nbrSheets = nbrSheets + 1
                SheetName = ws.Name
                ws.Range(Range("A2"), Range("A" & Cells.Rows.Count).End(xlUp)).Copy
                rwStart = rwEnd + 1
                wsCompare.Cells(rwStart, 1).Select
                Application.CutCopyMode = False
                rwEnd = Range("A" & Cells.Rows.Count).End(xlUp).Row
                For rw = rwStart To rwEnd
                    wsCompare.Cells(rw, 2) = SheetName
                    wsCompare.Cells(rw, 3) = rw - rwStart + 2
                Next rw
            End If
        Next ws
        rwEnd = Range("A" & Cells.Rows.Count).End(xlUp).Row
        For rw = 2 To rwEnd
            wsCompare.Cells(rw, 4) = WorksheetFunction.CountIf(Range(Cells(2, 1), Cells(rwEnd, 1)), Cells(rw, 1))
        Next rw
        wsCompare.Range(Cells(1, 1), Cells(rwEnd, 4)).Sort Key1:="SheetName", Order1:=xlAscending, Key2:="Row", Order2:=xlDescending, Header:=xlYes
        For rw = 2 To rwEnd
            If wsCompare.Cells(rw, 4) < nbrSheets Then
                SheetName = wsCompare.Cells(rw, 2)
                Range(Cells(wsCompare.Cells(rw, 3), 1), Cells(wsCompare.Cells(rw, 3), 1)).EntireRow.Delete Shift:=xlUp
            End If
        Next rw
        Application.DisplayAlerts = False
        Application.DisplayAlerts = True
    End Sub

    Open in new window

    LVL 1

    Author Comment

    OK. Thanks for the script. At the moment it seems to only output 3 rows into another worksheet which I assume are the non matching rows. Would it help for me to paste an example spreadsheet so you can see how its laid out- see attached. Im not sure if your script has only enabled comparison of a few rows or not. Also should I be called the sheet to make the comparison from something else? Eventually all sheets should compare to all sheets though.
    LVL 1

    Author Comment

    Looks like the attachment didn't work so here it is again
    LVL 20

    Accepted Solution

    In the question the values to compare (leftPos) was in column A, but in the file the values are in column B.
    A "minor" detail why it did not work with your real data :)

    The sheets are not matched to the others one at a time.
    Looping the sheets row by row, and searching the other sheets, to find a match on all, will be much too slow.
    Everything is done in one process for all rows on all sheets.

    The macro copy all values (row 2 down to the end) in column B from all worksheets, to a temporary worksheet column A, after each other, making one list with all the values to compare.
    At each copy, the sheet name and the row number is set in column B and C.
    Then in column D for each row, the value in column A (leftPos) is counted for the number of times in the list.
    The entire list column A to D, is sorted by sheet name and row number, descending to get the last rows first.
    Rows must be delete from bottom of the sheet.
    The list is looped, and if number of counts in column D is less than the number of sheets (= is not on all sheets), then the row (row number in column C) on the sheet (sheet name in column B) is deleted.
    Finally the temporary sheet is deleted.

    I added an info box at the end, to display how may rows on each sheet are removed, and tell when the process is finished.
    If there are many rows on each sheet, many sheet, and many rows to delete, a process indicator could be added, but with the data here, the process finish in a few seconds.
    LVL 1

    Author Closing Comment

    Super useful answer. Really helpful and thanks for doing it so quickly

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now