Be careful when using the Remove Duplicates tool in Excel. Remove duplicates DOES NOT work if number formats are different. It is very literal in that even if the format is different it will assume it is not a duplicate. So the numbers 10 and 10.00 will be seen as unique and not deleted. However, conditional formatting DOES see the correct items as duplicates (ignores the formatting).
Table of contents
YouTube Remove duplicate not working in Excel
When not finding duplicates is likely to be a problem
This shouldn’t be a problem if the data is from a system as they tend to be consistent in how they format each column. However, if it is based on human entry or a copy/ paste from another system/ source, it may well NOT delete all the true duplicates.
Remove duplicates NOT seeing duplicates
As shown below (1) is what we effectively want the Remove duplicates tool to see, however it may look like (2) or (3), especially if a human has been involved or data from different sources are put together. Normally Excel ignores formatting and looks at the truth of the number, but the Remove Duplicates tool does not, and can appear as if it is not working.
If we run the Remove Duplicates tool on the 3 blocks, this is what is left behind. Notice that the only difference in (2) and (3) is the way some of the numbers are formatted.
Does Conditional Formatting duplicates have the same problem?
Simple answer is NO. Below, using Conditional Formatting Duplicate values, in (2) and (3) you will see that all 4 cells are considered duplicates even though formatting is different.
Solution- Make all the formats the same
If you use Remove Duplicates, perhaps consider formatting the first row the way you want it and then using the format painter to make all of them the same. Remove Duplicates is more likely to get it correct if you do this.
Related
Go to the next duplicate in Excel
Excel not recognizing duplicates