Forum Discussion
Remove duplicates not working properly
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.
- 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
4 Replies
hello
may be it caused by using dash or underline etc. in your data, for ex.:
1234
1234-1
4321
- 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 worksThe 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!
