Deleting values in one file based on another file

[RRB]

Hi dear Friends!



Is there an easy way, that even a total-excel-idiot like myself can handle for:

file (or worksheet1) column 1

a1
a2
a3
a4

file (or worksheet2) column 1

a1
a2
a3
a4
a5
a6
a7

Run the function and now file (or worksheet) 2 looks like this:

a5
a6
a7

I tried asking Google and did not see anything I could understand

Thank you and have a good day!

Susan Flamingo

[Logit]

The following compares Sheet 1 vs Sheet 2 and finds anything in Sheet 1 that was not in Sheet 2.

I believe for your project you'll need to change (in the macro code) Sheet 1 to reflect Sheet 2 .... and Sheet 2 should be changed to Sheet 1.

Sub compare()
Application.ScreenUpdating = False
    Sheets(3).Activate  'Go to sheet 3
    Cells.Clear         'and clear all previous results
    
    Range("a1").Select  'set cursor at the top
    
    Sheets(1).Activate  'go to sheet 1
    Range("a1").Select  'begin at the top
    
   
    
    Dim search_for As String   'temp variable to hold what we need to look for
    Dim cnt As Integer         'optional counter to find out how many rows we found
    
    Do While ActiveCell.Value <> ""   'repeat the follwoing loop until it reaches a blank row
        
        search_for = ActiveCell.Offset(0, 1).Value   'get a hold of the value in column B
        
        Sheets(2).Activate  'go to sheet(2)
        
        On Error Resume Next   'incase what we search for is not found, no errors will stop the macro
        
        Range("b:b").Find(search_for).Select  'find the value in column B of sheet 2
        
        If Err <> 0 Then   'If the value was not found, Err will not be zero
        
            On Error GoTo 0  'clearing the error code
            
            Sheets(1).Activate   'go back to sheet 1
            
            r = ActiveCell.Row   'get a hold of current row index
            
            Range(r & ":" & r).Select  'select the whole row
            
            cnt = cnt + 1   'increment the counter
            
            Selection.Copy  'copy current selection
            
            Sheets(3).Activate  'go to sheet 3
            
            ActiveCell.PasteSpecial xlPasteAll  'Past the entire row to sheet 3
            
            ActiveCell.Offset(1, 0).Select  'go down one row to prepare for next row.
            
            
        End If
        Sheets(1).Activate   'return to sheet 1
        ActiveCell.Offset(1, 0).Select   'go to the next row
        
    Loop   'repeat
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    Sheets(3).Activate    'go to sheet 3 to examine findings
    
    
    MsgBox "I have found " & cnt & " rows that did not exist in sheet 2"
    
End Sub

[Alansidman]

One more way with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(Source, {"Column1"}, Source2, {"Column1"}, "Table2", JoinKind.RightAnti),
    #"Expanded Table2" = Table.ExpandTableColumn(MQ, "Table2", {"Column1"}, {"Column1.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Column1"})

in
    #"Removed Columns"

[RRB]

I appreciate your help.

Will this DELETE the values in sheet 2 that are also in sheet 1?

That is what I require. Sorry if I did not make myself clear in OP

Thank you and have a good day!

Susan Flamingo

[Alansidman]

If you are referring to the PQ solution, then the answer is yes. that is what you asked to do.

[RRB]

Thank you so very much for the assistance. I needed an immediate solution, so being the lazy Flamingo that I am I did a conditional format in Google sheets and deleted all the highlighted rows.

Thank you again and have a good day!
Susan Flamingo

[Pecoflyer]

Please mark thread " solved" ( see "thread tools"). Thx