• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Remove Duplicates

Hi Experts,

Can someone show me how to remove duplicates from the attached spreadsheet?

Cheers

Carlton
duplicates.xlsx
0
cpatte7372
Asked:
cpatte7372
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
MINDSUPERBCommented:
You can use the Remove Duplicates command as shown in the attached image.

Sincerely,
Ed
remove-duplicates.jpg
0
 
MINDSUPERBCommented:
There are no duplicates on Column A on the file you have attached.

Ed
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cpatte7372Author Commented:
Mindsuperb,

I tried the 'Remove Duplicates' before posting this request.

There are duplicates, e.g. there two ZMH. However, the 'Remove Duplicates' didn't pick it up.....
0
 
MINDSUPERBCommented:
Yes, you are right. I did not see it.

Do the trim function then after that you may apply the Remove Duplicates command.

=Trim(A1) then copy down

Sincerely,
Ed
0
 
cpatte7372Author Commented:
Ed,

Thanks for responding, but I don't understand how that will resolve this issue?

By doing the trim function I'm copying the cell into another row. If I do that all the way down then I will have duplicates.

I want to remove the duplicates in a single row.....
0
 
cpatte7372Author Commented:
I don't understand how the 'Remove Duplicates' didn't pick up the duplicates??
0
 
jimyXCommented:
There are duplicate yes but it is not exact duplicate:
667 =>"ZMH"
668 => "ZMH " there is a space at the end of the text here and thus they do not equal when matched

You have 335 spaces at various locations which will not match up with each others when compared.
You can select the entire column and click Ctrl+H (Find and Replace window), at "Find what" put a space and at "Replace with" do not put anything, click OK.

Now you can remove duplicates.
0
 
MINDSUPERBCommented:
Carlton,

Values in cells are sometimes misleading. There are values look equal but they are not as how Excel analyzes it. A good example is a space before the value, after the value and double spaces between two strings in a cell. This happens with the two ZMH in your sample. The space after one of the ZMHs causes them not to be equal.

The Trim function resolves the issue by taking out those spaces. So, after trimming it you may copy the whole column and do paste special -> Values Only into Column A. Then, delete the column the you have created for the Trim formula.

Finally, you can now appy Remove Duplicates without an issue.

Sincerely,
Ed
0
 
DaveCommented:
My duplicate master addin at http://www.experts-exchange.com/A_2123.html will handle the whitespaces without you needing to use any further working calcs. It also handles case insenstive searches, string replacements etc

Cheers

Dave
0
 
cpatte7372Author Commented:
Thanks chaps
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now