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

[Howardc1001]

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

[dangelor]

Possibly...

Please Login or Register  to view this content.

[Howardc1001]

Thanks Dangelor. Have tested your code and it works perfectly

[dangelor]

Glad to help!

[Marc L]

With a single loop a starter demonstration :

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 » !

[Howardc1001]

Thanks Marc. Code works perfectly

[Marc L]

Thanks for the rep' !

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