Remove duplicated data

Solved/Closed
SherlynPeh Posts 8 Registration date Tuesday August 5, 2014 Status Member Last seen August 14, 2014 - Aug 5, 2014 at 11:32 PM
SherlynPeh Posts 8 Registration date Tuesday August 5, 2014 Status Member Last seen August 14, 2014 - Aug 11, 2014 at 11:05 PM
Hi,

How do I remove duplicated data from 3 different worksheets in excel ?
Like I have a column of client names on each worksheets.
I need to combine all the client names from the 3 worksheets and create a new worksheet indicating a list of client names without duplicating the same client name more than once.

Thank you! :)

2 replies

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Aug 7, 2014 at 10:44 AM
Hi SherlynPeh,

The Advanced Filter (under the Data tab) has an option to keep unique records.

For more details check this recent thread:
https://ccm.net/forum/affich-760797-removing-duplicate-data#p762925

Best regards,
Trowa
0
SherlynPeh Posts 8 Registration date Tuesday August 5, 2014 Status Member Last seen August 14, 2014
Aug 8, 2014 at 02:14 AM

Hi Trowa,

I'd tried the above method but keep error. I don't quite understand why I cant copy to another worksheet. Thank you once again for your help :)
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Aug 11, 2014 at 11:43 AM
Hi Sherlyn,

My opinion is that Microsoft forgot to update the process because it can be done by Macro.

When recording the process you will get:
Range("H1:H4").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1:A4"), Unique:=True

Here range H1:H4 is your source data (which you want to filter) and range A1:A4 is the destination range. Now place your sheet reference in front of the destination range, like:
Range("H1:H4").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet2").Range("A1:A4"), Unique:=True

Now adjust the source and destination range and the sheet reference to match yours.

If this is not a 1 time action and you want it more dynamic, then let us know.

Best regards,
Trowa
0
SherlynPeh Posts 8 Registration date Tuesday August 5, 2014 Status Member Last seen August 14, 2014
Aug 11, 2014 at 10:35 PM
Hi Trowa,
Do you mean I have to create the vba codes for macro instead of recording it?
Is there any other ways to achieve the same result without using macro? Thank you so much for the prompt reply :)
Hope you have a nice day.
0
SherlynPeh Posts 8 Registration date Tuesday August 5, 2014 Status Member Last seen August 14, 2014
Aug 11, 2014 at 11:05 PM
Oh! I managed to find out the reason why I keep encounter error during advance filtering. Thanks for the guide along the way :)
0