Attached a list with material numbers which contain duplicates.
Now I sorted the list (not yet in the example to have my beginning situation) as one would do before using remove duplicates.
But after remove duplicates still a few materials are double.
For example 1234250-12
I checked the length of both cells but both length 10 so no blanks.
Does anybody know what the problem is?
Thanks.
I am not seeing the problem here in Excel 2017 365.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Well unfortunately I do see it in office 365 excel.....
How many lines do you have left after remove?
Should be 1894
Odd... I don't see the problem if I remove duplicates before sorting.
But if I sort and then remove duplicates, there are a few dups left... Looking into it further.
Edit: Disregard the first line of this post.
Last edited by 63falcondude; 10-18-2017 at 11:14 AM.
Also removing before sorting leaves you with 1907 values so still some duplicates (1894 is unique)
You're right, I stand corrected.
Looking into a solution as to why there are dups left.
Your example is not duplicated (1234250-12). I haven't completely scrutinised the list, but can't see any duplication at first glance. Will double check.
EDIT:
Excel 2016 (Windows) 32 bit
Y Y Y Y Y Y Y Y Y 1000185-115 Y 22225M Y 22225MJ Y 22235M Y 22235MJ Y Y Y Y Y Y Y Y Y Y 1000185-115 Y 22225M Y 22225MJ Y 22235M Y 22235MJ Y
Sheet: Sheet1
Last edited by AliGW; 10-18-2017 at 10:58 AM.
I have to admit, I have never seen this before.
If I remove duplicates, there are some duplicates left.
I have to sort the list and then remove duplicates again for all of the duplicates to be removed.
By the way, your profile says you are using 2010 - please update it.
Ali, can you confirm whether or not you are experiencing the same behavior?
1) Open the workbook from post #1
2) Highlight all of column A and Remove Duplicates (shows 1907 unique values remaining)
3) Sort column A (you can see that the first 2 rows are dups)
4) Remove duplicates again (shows 1894 unique values remaining)
After step 2, you can run a COUNTIF formula in column B and see that there are duplicates left.
Last edited by 63falcondude; 10-18-2017 at 11:20 AM.
Yes, if I follow your steps. The list I posted above was what was left when I copied the unsorted list from A to F and removed duplicates, then did a count check to see which were still duplicated - I filtered the list to copy it here.
So the question would then be why are those duplicates left after running Remove Duplicates?
After a little bit of research, I have found that the built-in Remove Duplicates function can be unreliable, especially when the data is unsorted and has a mix of numbers and text.
The weird thing is that even if I sort it before removing dups, it doesn't find them all in the first go.
Interesting. I have never had a problem with it, and I use it a lot, although not on mixed lists - mostly on pure text.
Same. Which leads me to wonder if I have made any errors in the past due to this.
It seems that the most reliable ways of removing duplicates is either through code or formulas.
rpinxt, I do not have an answer as to why the built-in Remove Duplicates feature is not reliable. That being said, here is a formulaic approach:
B2 =COUNTIF(A$2:A2,A2) copied down column B
Filter column B to show values > 1
Delete those rows.
Hmmm ok.
So the conclusion is that build in remove duplicates is unreliable on mixed lists.
And that using a formula for it is the workaround?
Yup - so it would seem.
You might be able to record a macro to do it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks