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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
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