May 17 2021 06:52 PM
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.
May 21 2021 01:21 PM
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))-1
it 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.
Jun 07 2021 01:31 PM
@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.
Jun 07 2021 06:13 PM
@Eric Starker This question was excel Microsoft related.....TQ
Jun 07 2021 06:23 PM