Copy complete row to new sheet if duplicate entry is Not Found in other sheet

leebowman

New Member
Joined
Sep 3, 2014
Messages
1
Hello there, Have been using this forum and various responses to achieve my goal with limited success so far. I have 2 spreadsheets of information and we are trying to find entries that only exist on the Master and copy them to a new sheet. Sheet 1 contain a number of columns of useful information - Column A is the ID. The same ID may be found in Sheet 2. However if that same ID is not found then we would like the complete Row to be copied to Sheet 3. The end result shows a list of machines that are in the Master Sheet 1 and not found in Sheet 2. Any thought greatly appreciated
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hello
Assuming Sheet 1 is your Master, insert a new (temporary) column, and add A VLOOKUP formula that looks up the Master ID on Sheet 2 and returns it to the cell. When complete, value out the look up formulae, so that you can sort on the values.
Then copy all the rows with #N/A entries to Sheet 3. Re-sort the sheets back to their original state as required, and delete the extra column you added.
 
Upvote 0
Code:
Sub moveRows()

    Dim startRow As Integer
    Dim endRow As Long
    Dim nextRow As Long
    
    Dim searchArea As Range


    Set searchArea = Sheets(2).Range("A1:A" & Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row)
    
    startRow = 2
    endRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
    
    nextRow = 1
    With Sheets(1)
        For x = startRow To endRow
            For Each iCell In searchArea
                If .Cells(x, 1) = iCell Then
                    GoTo foundMatch
                End If
            Next iCell
                Sheets(3).Cells(nextRow, 1).EntireRow.Value = .Cells(x, 1).EntireRow.Value
                nextRow = nextRow + 1
foundMatch:
        Next x
    End With


End Sub
 
Upvote 0

Forum statistics

Threads
1,225,074
Messages
6,182,715
Members
453,132
Latest member
nsnodgrass73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top