Moving Duplicate records from one worksheet to another

[Beatrix]

Hi Everyone ,

I have got a list which has duplicate records based on the data in column A. What I need to do is cut and paste those records into the new worksheet. Can anyone help me on this please? I attached a sample file with before after scenario.

Cheers
B.

[snb]

Sub M_snb()
sp = Sheets("before").Cells(1).CurrentRegion
sn = Filter([transpose(if(A1:A200="","~",if(countif($A$1:$A$200,A1:A200)=1,row(A1:A200),"~")))], "~", False)

Sheets("after").Cells(20, 1).Resize(UBound(sn) + 1, UBound(sp, 2)) = Application.Index(sp, Application.Transpose(sn), Array(1, 2, 3, 4))
End Sub

[Beatrix]

Thanks very much for you reply snb. But I need to cut and paste those records into the new tab. Lets say I have entire list in before worksheet. After worksheet is blank. When I run the script duplicates must be removed from before list and paste to the after list. At the end, I should have unique records (based on column A) in before tab and dups in after tab.

This script doesn't do that :(

Cheers
B.




Sub M_snb()
sp = Sheets("before").Cells(1).CurrentRegion
sn = Filter([transpose(if(A1:A200="","~",if(countif($A$1:$A$200,A1:A200)=1,row(A1:A200),"~")))], "~", False)

Sheets("after").Cells(20, 1).Resize(UBound(sn) + 1, UBound(sp, 2)) = Application.Index(sp, Application.Transpose(sn), Array(1, 2, 3, 4))
End Sub

[snb]

I showed you a method.

So you will have to adapt the code: the best way to master VBA.

[Beatrix]

Thanks I understand that and I did adapt it to my code but it doesn't copy paste right records. If data in column A (lets say C1468) in before list does repeat then those 2 rows need to be moved to the new tab. When I use your method it just lists the unique records in new tab not the dups.

Here is the script I modified.


Sub M_snb()
sp = Sheets("master2").Cells(1).CurrentRegion
sn = Filter([transpose(If(A1:A1000="","~",If(countif($A$1:$A$1000,A1:A1000)=1,row(A1:A1000),"~")))], "~", False)

Sheets("master3").Cells(1, 1).Resize(UBound(sn) + 1, UBound(sp, 2)) = Application.Index(sp, Application.Transpose(sn), Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11))
End Sub





I showed you a method.

So you will have to adapt the code: the best way to master VBA.

[snb]

to retrieve the dups ( it's only 1 character difference):


Sub M_snb()
sp = Sheets("master2").Cells(1).CurrentRegion
sn = Filter([transpose(If(A1:A1000="","~",If(countif($A$1:$A$1000,A1:A1000)>1,row(A1:A1000),"~")))], "~", False)

Sheets("master3").Cells(1, 1).Resize(UBound(sn) + 1, UBound(sp, 2)) = Application.Index(sp, Application.Transpose(sn), Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11))
End Sub

[mancubus]

@snb
if Sheets("before") is not active sheet, it throws "run time error 13, type mismatch."
any comments as to why?




and consolidation of subprocedures can be as follows:



Sub M_snb()

With Sheets("before")
.Activate
sp = .Cells(1).CurrentRegion
End With

sn = Filter([Transpose(If(A1:A15000="","~",If(CountIf($A$1:$A$15000,A1:A15000)=1,Row(A1:A15000),"~")))], "~", False)
sq = Filter([Transpose(If(A1:A15000="","~",If(CountIf($A$1:$A$15000,A1:A15000)>1,Row(A1:A15000),"~")))], "~", False)

With Sheets("before")
.Cells(1).CurrentRegion.ClearContents
.Cells(1).Resize(UBound(sn) + 1, UBound(sp, 2)) = Application.Index(sp, Application.Transpose(sn), Array(1, 2, 3, 4))
End With

Sheets("duplicate records").Cells(1).Resize(UBound(sq) + 1, UBound(sp, 2)) = Application.Index(sp, Application.Transpose(sq), Array(1, 2, 3, 4))

End Sub

[Beatrix]

got it ">" :hi: thanks snb!!!!:bow: I wish I could have your knowledge :)


to retrieve the dups ( it's only 1 character difference):


Sub M_snb()
sp = Sheets("master2").Cells(1).CurrentRegion
sn = Filter([transpose(If(A1:A1000="","~",If(countif($A$1:$A$1000,A1:A1000)>1,row(A1:A1000),"~")))], "~", False)

Sheets("master3").Cells(1, 1).Resize(UBound(sn) + 1, UBound(sp, 2)) = Application.Index(sp, Application.Transpose(sn), Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11))
End Sub

[snb]

@mancubus

I always put the macro that works with the data in a certain sheet in the codemodule of that sheet.
In this case the macro resides in the code module of sheet 'before'.
I rarely create macromodules.

[mancubus]

@snb

thanks for the reply.
apparently ranges in formulas without sheet references assume active sheet.
but when i tried adding worksheet reference to the formulas it did't work either.


sn = Filter([Transpose(If(before!A1:A15000="","~",If(CountIf(before!$A$1:$A$15000,before!A1:A15000)=1,Row(A1:A15000),"~")))], "~", False)

[mancubus]

_added_to_previous_post_

[snb]

See the attachment

[mancubus]

thanks.

since the only difference is the number of rows in range references (200 vs 15000), testing for the maximum number before running the code might be important.



is it deja vu did i somehow ask the same questions before? :)

[snb]

@mancubus:

You autofilled data to row 15000; resulting in no unique records.
In that case the code errors out. Not because of the amount of rows nor because of referring to a sheet, nor because of the limitations of transpose.

[mancubus]

@snb

actually i tried it with the OP's file.
it's working now on the same file now.

thanks for your time and explanation.