PDA

View Full Version : Moving Duplicate records from one worksheet to another



Beatrix
10-22-2014, 03:02 AM
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
10-22-2014, 04:33 AM
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
10-22-2014, 05:15 AM
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
10-22-2014, 05:29 AM
I showed you a method.

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

Beatrix
10-22-2014, 05:44 AM
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
10-22-2014, 05:55 AM
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
10-22-2014, 06:24 AM
@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
10-22-2014, 06:25 AM
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
10-22-2014, 01:02 PM
@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
10-22-2014, 11:48 PM
@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
10-22-2014, 11:54 PM
_added_to_previous_post_

snb
10-23-2014, 04:29 AM
See the attachment

mancubus
10-23-2014, 04:49 AM
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
10-23-2014, 06:19 AM
@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
10-23-2014, 07:04 AM
@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.