your countif did not work b/c "floating point" issue in excell : Excel can not read big value, from 15 digits and upper
Values those exceed 15 digits, to compare to other one, need to be splitted into two part: 1st part is first 15 digits, 2nd part is the rest
If using countif, it should be countif(S) to count both condition 1st 15 and the rest ALL match together
Another option is using: MATCH=ROW
any dupliacte value has its own row_index, but has same MATCH_index in specific range
For example, A2="abc", A3="abc"
1st "abc" has row_index is row(A2)=2, 2nd one is row(A3)=3
but both "abc" has match_index in range A2:A10 is 1
So to mark the 1st one and ignore the 2nd one, try MATCH(A2:A3,A2:A10)=ROW(A2:A10)
equals:
{2,2,..,.,..}={2,3,...,...}
in pairs, one by one:
{true,false,..,...,...,..}
Bookmarks