+ Reply to Thread
Results 1 to 8 of 8

Using COUNTIF with partial strings to generate unique ID

  1. #1
    Registered User
    Join Date
    09-20-2020
    Location
    Yeovil, Somerset
    MS-Off Ver
    MS Office 365 Subscription
    Posts
    15

    Using COUNTIF with partial strings to generate unique ID

    Hi,

    I'm creating unique IDs for each row in a data sheet by combining CONCATENATE with COUNTIF.

    So far, I have this formula: =CONCATENATE(LEFT(B2,4),LEFT(F2,2),LEFT(E2,3),"-",COUNTIF($E$2:E2,E2))

    This works fine EXCEPT, in column E, it only creates a different identifier if the entire cell has identical content.

    I would like to count only the first 3 characters of the text in column E.

    I have tried nesting the LEFT function within the COUNTIF, but it's not working.

    Can anyone help?

    Thanks
    Sara

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Using COUNTIF with partial strings to generate unique ID

    Hi,
    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Using COUNTIF with partial strings to generate unique ID

    How about
    =CONCATENATE(LEFT(B2,4),LEFT(F2,2),LEFT(E2,3),"-",COUNT(FILTER(ROW(E$2:E2),LEFT(E$2:E2,3)=LEFT(E2,3))))

  4. #4
    Registered User
    Join Date
    09-20-2020
    Location
    Yeovil, Somerset
    MS-Off Ver
    MS Office 365 Subscription
    Posts
    15

    Re: Using COUNTIF with partial strings to generate unique ID

    Quote Originally Posted by belinda200 View Post
    Hi,
    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    My apologies, Belinda200 - only my 2nd post and still learning.
    Please find sample excel sheet attached - it is the pairs in highlighted colours that my query applies to - as you can see the Unique ID is not unique because the formula I have reads the entire cell in Column E - I need it to read only the first 3 characters.

    Incidentally, I have a secondary query - in the final row, the Unique ID has a space in it, because it reflects Column E. Is there an easy way around this, where the function 'ignores' a space and picks up the next proper character? I suspect not, because it would go against the logic of specifying the first 3 characters.
    But no harm in checking....

    If I need to put this secondary query in another thread, please do say - I don't want to annoy anyone!

    Many thanks
    Sara
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-20-2020
    Location
    Yeovil, Somerset
    MS-Off Ver
    MS Office 365 Subscription
    Posts
    15

    Re: Using COUNTIF with partial strings to generate unique ID

    Quote Originally Posted by Fluff13 View Post
    How about
    =CONCATENATE(LEFT(B2,4),LEFT(F2,2),LEFT(E2,3),"-",COUNT(FILTER(ROW(E$2:E2),LEFT(E$2:E2,3)=LEFT(E2,3))))
    Hi Fluff13,

    Fantastic - that works a treat!! Thank you so much :-)
    I just posted a sample as per Belinda200's request - there's a secondary query there, fyi - but this was the main one.

    Thanks again - you have saved me hours!
    Sara

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Using COUNTIF with partial strings to generate unique ID

    To deal with the space try
    =CONCATENATE(LEFT(B2,4),LEFT(F2,2),LEFT(SUBSTITUTE(E2," ",""),3),"-",COUNT(FILTER(ROW(E$2:E2),LEFT(SUBSTITUTE(E$2:E2," ",""),3)=LEFT(SUBSTITUTE(E2," ",""),3))))

  7. #7
    Registered User
    Join Date
    09-20-2020
    Location
    Yeovil, Somerset
    MS-Off Ver
    MS Office 365 Subscription
    Posts
    15

    Re: Using COUNTIF with partial strings to generate unique ID

    Fluff13 - you are an angel!!

    It works perfectly.
    Again, thank you so much.

    Best, Sara

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Using COUNTIF with partial strings to generate unique ID

    Glad to help & thanks for the feedback.

+ 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. Partial Strings Match
    By jagadeesh.rt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-27-2015, 06:13 PM
  2. Counting partial strings
    By darkhangelsk in forum Excel General
    Replies: 3
    Last Post: 07-31-2009, 11:24 PM
  3. Using partial strings as argument for sumif()
    By Andy Mail in forum Excel General
    Replies: 3
    Last Post: 07-16-2009, 04:11 PM
  4. Replies: 5
    Last Post: 07-15-2009, 10:59 AM
  5. MATCH on partial strings
    By ffffloyd in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2008, 05:26 AM
  6. [SOLVED] Summing partial strings.
    By [email protected] in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-08-2005, 07:05 AM
  7. [SOLVED] Sumproduct and finding partial strings
    By Jeff in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-06-2005, 01: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