Compare Ref in Col B on Sheet1 to sheet 2 and where the no match copy data to sheet2

I have data on sheet "Data Import"


I want to compare the refrence in Col B from row 2 onwards to that in Sheet "Prev Data" and where there in no match I want the data from sheet "Import Data" copied as follows to Sheet "Prev Data"

1) Col A:D sheet "Imported Data" to be copied to Cols A:D on Sheet "Prev data"

2) Col E on Sheet "Imported data" to be copied to Col F on sheet "prev Data"


I have attached sample data

Your assistance is most appreciated

Possibly...

Please Login or Register  to view this content.

Thanks Dangelor. Have tested your code and it works perfectly

Glad to help!

With a single loop a starter demonstration :

PHP Code: 

Sub Demo1()
  Const F = "TRANSPOSE(IF(ISNA(MATCH(#,¤,0)),ROW(#)))", S = "Prev Data"
    Dim R&, A$, V
        R = Sheets(S).Cells(Rows.Count, 2).End(xlUp).Row
        A = Sheets(S).Range("B1:B" & R).Address(External:=True)
        Application.ScreenUpdating = False
    With Range("'Imported Data'!A1").CurrentRegion
        For Each V In Filter(.Parent.Evaluate(Replace( Replace(F, "#", .Columns(2).Address), "¤", A)), False, False)
            R = R + 1
           .Rows(V).Columns("A:D").Copy Sheets(S).Cells(R, 1)
           .Cells(V, 5).Copy Sheets(S).Cells(R, 6)
        Next
    End With
        Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Thanks Marc. Code works perfectly

Thanks for the rep' !

Can be achieved too without any loop just using an Excel basics : an advanced filter …