+ Reply to Thread
Results 1 to 4 of 4

Count Unique (Long) Strings

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    64

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,776

    Re: Count Unique (Long) Strings

    Please try at C2

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

  3. #3
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    64

    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. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    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,..,...,...,..}
    Last edited by bebo021999; 01-15-2021 at 10:26 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 20
    Last Post: 08-24-2017, 02:40 AM
  2. Match different long strings with an array of short, summarizing strings
    By FKemps in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-24-2015, 12:55 AM
  3. [SOLVED] Formula to count unique strings broken down by month
    By Cabs1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2014, 10:34 AM
  4. Replies: 12
    Last Post: 07-03-2012, 04:50 PM
  5. Replies: 5
    Last Post: 02-15-2012, 09:57 AM
  6. Long Strings
    By Guitar Billy in forum Excel General
    Replies: 1
    Last Post: 11-20-2006, 10:03 AM
  7. [SOLVED] Count Unique Strings
    By stacy_jeanne@yahoo.com in forum Excel General
    Replies: 4
    Last Post: 04-27-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1