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

Posted on 2014-08-02
Medium Priority
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
  • 3
  • 2
LVL 24

Expert Comment

by:Ejgil Hedegaard
ID: 40236679
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


Author Comment

ID: 40237230
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.

Author Comment

ID: 40237231
Looks like the attachment didn't work so here it is again
LVL 24

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 40237386
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.

Author Closing Comment

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

830 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