Delete matching records but some duplicated.

[tweacle]

Hi

I dont know where to start on this one.

I have a workbook with 2 sheets and data in columns A-D.What im trying to do is when there is a match exactly the same on both sheets I need the row deleting from sheets 1 & 2. The issue I have is that sometimes there is 1 row of data on sheet 1 and 2 or 3 rows with the same data in sheet 2 and I dont need both rows of data deleting in sheet 2 and only 1 as there is only 1 entry on sheet 1.

The data will always be in columns A-D but spread over different rows over the worksheet. I.E lets say sheet 1 row 99 matches data in sheet 3 row 400 then I need both rows deleting.

Is there anyway I can do this

[Trebor76]

Hi tweacle,

Try this (initially on a copy of your data as the results cannot be undone if they're not as expected):

Please Login or Register  to view this content.

After I ran the code only 45631 in Sheet2 remained. Note the data for both sheets is only across columns A to C not A to D as you say?

Thanks,

Robert

[tweacle]

Thanks but it dont seem to be working unless its me.


In the enclosed workbook Im using (attached) on sheet 2 there is more value than whats on sheet 1.

What you would think that sheet 2 has duplicated data of which is one reason why but also there could be data in sheet 2 that is not in sheet 1 causing the difference too.
There are occasions that sometimes there are duplicated entries on sheet 2 but they are not duplicated as there are 2 entries on sheet 1 too

Im trying to get a macro that leaves all the entries that are on sheet 2 as duplicated or not on sheet 1 listed.

I have posted this on here too

https://www.mrexcel.com/board/thread...1135692/page-3

[Marc L]

According to your last attachment - original is too poor - as a VBA beginner starter :

Sub Demo1() 
  Const C = 5, F = "A2:A#&""¤""&B2:B#&""¤""&C2:C#&""¤""&D2:D#" 
    Dim R&, V(1 To 2), W(1 To 2), X 
    For R = 1 To 2 
        V(R) = Sheets(R).Evaluate(Replace(F, "#", Sheets(R).[A2].End(xlDown).Row)) 
        W(R) = Evaluate("{0" & Application.Rept(";0", UBound(V(R)) - 1) & "}") 
    Next 
    For R = 1 To UBound(V(1)) 
        X = Application.Match(V(1)(R, 1), V(2), 0) 
        If IsNumeric(X) Then W(1)(R, 1) = 1: W(2)(X, 1) = 1: V(2)(X, 1) = Empty 
    Next 
       X = Application.Sum(W(1)) 
    If X Then 
            Application.ScreenUpdating = False 
        For R = 1 To 2 
            With Sheets(R).[A2].CurrentRegion.Rows 
                .Columns(C) = W(R) 
                .Resize(, C).Sort .Columns(C), xlAscending, Header:=xlNo 
                 Union(.Item(.Count + 1 - X & ":" & .Count), .Columns(C)).Clear 
            End With 
        Next 
            Application.ScreenUpdating = True 
    End If 
End Sub  

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

[Trebor76]

This is a lot different...

I have a workbook with 2 sheets and data in columns A-D.What im trying to do is when there is a match exactly the same on both sheets I need the row deleting from sheets 1 & 2

...from this:

Im trying to get a macro that leaves all the entries that are on sheet 2 as duplicated or not on sheet 1 listed

That said try this (again on a copy of your data as the results cannot be undone if they're not as expected):

Please Login or Register  to view this content.