Forum Discussion
How to Remove Actual Duplicate Values
Problems!!!
I have a database. Where there is a column called NID. NID numbers are usually 10, 13 and 17 digits.
After applying the duplicate value in the NID column, I see that if the 10 and 13 digit NID numbers are more than one, it detects exactly as a duplicate value.
But in the case of 17 digits the opposite
If the 17-digit NID number does not have more than one, then it can be seen that some 17-digit NID numbers are detected as duplicate values even though they are not duplicated.
6 Replies
azmat4u Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Microsoft Excel discussion space - please post Microsoft Excel questions here in the future.
EricStarker This question was excel Microsoft related.....TQ
- Correct, but you originally posted it in the Tech Community Discussion space which is about the Tech Community website ONLY (i.e. techcommunity.microsoft.com), not about Microsoft products.
Now it's been moved to the Excel discussion space which is about Excel!
In this case ID numbers are saved as text, but counting duplicates Excel interprets such texts as numbers, for which it keeps 15 digits maximum. If work with numbers using the rule with formula
=SUMPRODUCT(1*(C3=$C$3:$C$1489))-1it operates with 17 digits maximum and returns duplicates correctly.
If you have more than 17 digits that doesn't work as well. If so it'll be more reliable to create helper column with texts like ="abc" & C3 and check duplicates on this column.
Please check attached.
- Allen
Community Manager
Is this for Microsoft Access?- Microsoft Excel
