SOLVED

Remove duplicates not working properly

%3CLINGO-SUB%20id%3D%22lingo-sub-1183086%22%20slang%3D%22en-US%22%3ERemove%20duplicates%20not%20working%20properly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183086%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20already%20searched%20this%20forum%20about%20this%20problems%20but%20so%20far%20I%20have%20not%20found%20a%20good%20answer.%3C%2FP%3E%3CP%3EI%20am%20working%20with%20several%20.csv%20files%20with%20have%20around%2010%20rows%20and%202k%20lines%20and%20I%20have%20to%20remove%20duplicated%20values.%20Every%20duplicated%20value%20in%20each%20row%20repeats%20itself%20in%20every%20duplicated%20line%2C%20so%20this%20is%20not%20a%20problem.%20For%20example%2C%20the%20row%20A%2C%20B%20and%20C%20has%20the%20values%20X%2C%20Y%2C%20and%20Z%20in%20the%20line%201%2C%20so%20its%20duplication%20has%20the%20same%20X%2C%20Y%2C%20and%20Z%20in%20a%20different%20line%20but%20under%20the%20same%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20when%20I%20select%20all%20of%20the%20rows%20and%20lines%20and%20go%20to%20Data%20%26gt%3B%20Remove%20Duplicates%20and%20finally%20execute%20the%20action%2C%20the%20excel%20returns%20me%20a%20message%20that%20N%20duplications%20were%20found%20and%20removed%2C%20when%20actually%20just%20one%20or%20other%20indeed%20were.%20If%20I%20try%20to%20run%20it%20again%20the%20message%20I%20receive%20is%20that%20there%20is%20no%20more%20duplicated%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20OS%20MAC%20Catalina.%3C%2FP%3E%3CP%3EOffice%20365%2C%20Excel%20Version%2016.34%20(20020900).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1183086%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1183122%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20duplicates%20not%20working%20properly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183122%22%20slang%3D%22en-US%22%3EHello%2C%20most%20likely%20some%20of%20the%20text%20has%20extra%20spaces.%20I%20will%20advice%20deploy%20the%20%3DTRIM%20function.%3CBR%20%2F%3E%3CBR%20%2F%3ELet%20say%20you%20have%20all%20the%20values%20you%20want%20to%20Remove%20Duplicates%20in%20column%20A1%3AA100.%20In%20column%20B1%2C%20deploy%20%3DTRIM(A1).%3CBR%20%2F%3ECopy%20down%20the%20formula.%3CBR%20%2F%3E%3CBR%20%2F%3EThen%2C%20rum%20the%20Remove%20Duplicates%20on%20Column%20A.%3CBR%20%2F%3E%3CBR%20%2F%3ELet%20me%20know%20whether%20this%20works%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1183461%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20duplicates%20not%20working%20properly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183461%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20trim%20wasn't%20the%20problem%20itself%2C%20but%20I%20am%20glad%20you%20made%20me%20remember%20it.%20However%2C%20while%20thinking%20about%20your%20advice%20of%20%22cleaning%22%20up%20the%20cells%20I%20realized%20that%20there%20were%20few%20characters%20occupying%20cells%20out%20of%20the%20regulares%20columns.%20As%20I%20wasn't%20expecting%20it%2C%20I%20have%20always%20been%20selecting%20%22whole%20columns%22%20in%20the%20remove%20duplicate%20option%20and%20this%20few%20characters%20have%20been%20interfering%20the%20results.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20is%20the%20real%20problem.%20If%20I%20need%20any%20more%20advice%20I%20will%20be%20back%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20help%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1183492%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20duplicates%20not%20working%20properly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183492%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F563503%22%20target%3D%22_blank%22%3E%40Mcelli%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou're%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2239080%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20duplicates%20not%20working%20properly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2239080%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%3C%2FP%3E%3CP%3Emay%20be%20it%20caused%20by%20using%20dash%20or%20underline%20etc.%20in%20your%20data%2C%20for%20ex.%3A%3C%2FP%3E%3CP%3E1234%3C%2FP%3E%3CP%3E1234-1%3C%2FP%3E%3CP%3E4321%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

 

4 Replies
best response confirmed by Mcelli (New Contributor)
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

 

 

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!

@Mcelli 

 

You're welcome

hello

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

1234

1234-1

4321