VBA to remove duplicates from a sheet and save them on another

[jeffwest2]

I apologise if this is somewehere already on here but i can't find it.

I have a sheet of data that has duplicates, the sheet can be of any length.

The duplicates can either by Name, HouseName/Number and postcode or Phone number.

What I want to be able to do is run some VBA that does one pass through looking at the Name, HouseName/Number and postcode, where they appear more then once remove all bar 1 onto another sheet, then do a second pass looking only at the phone number but remove all of these to another sheet not keeping any on the first sheet (two requirements really).

I can find code that helps to find and remove but not to copy them to another sheet, and it's a bit of a frustration. Any help is very graefully appreciated.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[Andrzejek]

"I can find code that helps to find and remove" - so, what's the code? You can build on that (modify the code) to do what you want.

Have fun.

---- Andy

 

[jeffwest2]

I can, but I am stuck on this, I will try and add the code here that i am using to find and remove them, what i actually want to do i guess is copy them first.

Because I also need to reformat the data first as well I am trying to do 6 different things at once and just hoped that someone had some code that would solve the issue I have with needing to take a copy of the duplicates to send back to my client to say what I removed from the file.

I am on something else at the moment, but will try and post the code i have later.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[Andrzejek]

That's my whole point: if you (may) already have code to remove some records, somebody can help you write those records somewhere before the delete takes place.

But you have to show some work before somebody will help you....

Have fun.

---- Andy

 

[SkipVought]

Please post your code and explain exactly how it is or is not workbin for you. DETAILS & cogent INFORMATION, please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest2]

Sorry, I was being a bit lazy really, I have used a work around that I can live with for now.

By adding a new column to the sheet then adding =COUNTIF(U$2:U2,U3)=0 in every cell down the sheet then using conditional formatting to highlight the 'False' then filter on just the false ones.

I wanted to try and find another way that would do it for me, in the end I went back to a way I did it some time ago with something else, I was trying to see if anyone had some code I could use to transfer them to another sheet as that's the bit I was stuck on, i have done something similar before using a copy/delete row command vba code, I just couldn't find or remember it off hand and didn't have access to some files I had that I was sure it is in.

Thanks anyway.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[SkipVought]

Why not
[tt]
=COUNTIF(U$2:U2,U3)
[/tt]
which, if you filter this column on >0, lists each ADDITIONAL duplicate, that is a candidate to 'move'.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}