# Count Unique (Long) Strings

1. ## Count Unique (Long) Strings

I'm trying to count unique values of long numbers-stored-as-text strings that exceed the 16-digit countif accuracy limit (sample attached). Can someone please help explain what formula I need to use? Assuming maybe something with sumproduct but am not finding results - any help appreciated. Thanks!

2. ## Re: Count Unique (Long) Strings

=N(MATCH(A2,A\$2:A2,)=ROWS(A\$2:A2))

3. ## Re: Count Unique (Long) Strings

Thanks! This works perfectly, @Bo_Ry, but I don't understand why - I get the match part but am confused how a =ROWS add-on makes this provide a unique count - can you explain how this formula works?

4. ## Re: Count Unique (Long) Strings

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

``Please Login or Register  to view this content.``
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,..,...,...,..}

There are currently 1 users browsing this thread. (0 members and 1 guests)