Excel if cell from two different sheets match delete line

Question

I have two different sheets, query and formonth, in same workbook. Columns b on both sheets have the data i want to compare. If column b on formonth matches any cell in column b on query sheet, i want to delete the entire row on the query sheet. I have tried so many different ideas with no success.

Answer 1

It's a tough one and will most likely to be a VBA solution.

This code should work (it's definitely not the prettiest and may be pretty clunky depending on the size of your datasets) but hopefully someone may provide you with something a bit smoother in the future.

Sub DeleteRows()

Dim QueryRange As Range
Dim FormonthRange As Range
Dim y As Integer

Application.ScreenUpdating = False

y = Worksheets("formonth").UsedRange.Rows.Count

For Each QueryRange In Worksheets("query").Range("B1:" & "B" & y)
'This is the range of cells to check

    For Each FormonthRange In Worksheets("formonth").Range("B1:" & "B" & y)
    'This is the range of cells to compare

        If QueryRange.Value = FormonthRange.Value Then
            QueryRange.Offset(, 1).Value = "@"
            Exit For
            End If

        Next FormonthRange

    Next QueryRange

Worksheets("query").Columns("C").SpecialCells(xlConstants, 2).EntireRow.Delete

Application.ScreenUpdating = True

End Sub

This code sets a column (in this case column C) to be the @ symbol if it detects a match between your Query tab and your Formonth tab. It then deletes rows based on the column that has the symbol.

Hopefully the way this is written should allow you to copy and paste. The only thing you need to change is the column that it places the @ symbol because it places the symbol as an offset to column B. These are the following lines (switch out EnterTheNumberYouWantHere and EnterColumnLetterHere)

QueryRange.Offset(, EnterTheNumberYouWantHere).Value = "@"

and

Worksheets("query").Columns("EnterColumnLetterHere").SpecialCells(xlConstants, 2).EntireRow.Delete

Where a few typical matching values are as follows:

If Offset = 1 Column = C (as shown in the code above)
If Offset = 2 Column = D
If Offset = 10 Column = L
If Offset = 15 Column = Q
Etc.

Basically pick a column that will always be blank in your spreadsheet and work from there.

Answer 2

You can use the free Microsoft Excel add-In Power Query (from Excel 2010) to pick the records, which don't match in the query sheet. What you try to get is to make an anti-join between both sheets

1. Define both data sources as a table and give them the name query and formonth.

2. Load the reference table formonth to Power Query (Power Query Ribbon > from table)
   In the advanced editor you will get a code similar to this one:

   let
       Source = Excel.CurrentWorkbook(){[Name="formonth"]}[Content]
   in
       Source
   

   Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

3. Then you merge both tables using a left anti-join. It's well described in Excelguru's Blog respectively on the MS Support Website

   Load the table query to Power Query and perform the left anti-join (Below the M Code).

   let
       Source = Excel.CurrentWorkbook(){[Name="query"]}[Content],
       #"Merge source (anti-join)" = Table.NestedJoin(Source,
                   {"B"},formonth,{"B"},"NewColumn",JoinKind.LeftAnti),
       #"Remove columns" = Table.RemoveColumns(#"Merge source (anti-join)",{"NewColumn"})
   in
       #"Remove columns"
   

   Remove the new column from the Merge Operation
   Go to Home –> Close & Load –> Close & Load

Here, how it looks like between both data sources and the Power Query output: