Remove duplicates not working properly

[Mcelli]

Hello there,

I have already searched this forum about this problems but so far I have not found a good answer.

I am working with several .csv files with have around 10 columns and 2k lines and I have to remove duplicated values. Every duplicated value in each column repeats itself in every duplicated line, so this is not a problem. For example, the columns A, B and C have the values X, Y, and Z in the line 1, so its duplication has the same X, Y, and Z in a different line but under the same column.

The problem is that when I select all of the rows and lines and go to Data > Remove Duplicates and finally execute the action, the excel returns me a message that N duplications were found and removed, when actually just one or other indeed were. If I try to run it again the message I receive is that there is no more duplicated values.

I am using OS MAC Catalina.

Office 365, Excel Version 16.34 (20020900).

Thank you in advance.

[#2 · Accepted solution]

Hello, most likely some of the text has extra spaces. I will advice deploy the =TRIM function.

Let say you have all the values you want to Remove Duplicates in column A1:A100. In column B1, deploy =TRIM(A1).
Copy down the formula.

Then, rum the Remove Duplicates on Column A.

Let me know whether this works

[#3]

The trim wasn't the problem itself, but I am glad you made me remember it. However, while thinking about your advice of "cleaning" up the cells I realized that there were few characters occupying cells out of the regulares columns. As I wasn't expecting it, I have always been selecting "whole columns" in the remove duplicate option and this few characters have been interfering the results. 

I hope this is the real problem. If I need any more advice I will be back here.

Thank you for the help, @Abiola1!

[#4]

@Mcelli 

You're welcome

[#5]

hello

may be it caused by using dash or underline etc. in your data, for ex.:

1234

1234-1

4321