+ Reply to Thread
Results 1 to 15 of 15

Help with CONCATENATE and COUNTIF to create automatic reference number

  1. #1
    Registered User
    Join Date
    04-16-2021
    Location
    Exeter, England
    MS-Off Ver
    2016
    Posts
    9

    Help with CONCATENATE and COUNTIF to create automatic reference number

    Hi all

    I am trying to create a reference number automatically using the following formula:

    =UPPER(CONCATENATE(LEFT(R2,4),"-",COUNTIF($R$2:R2,R2)))

    ...where column R contains a name formatted like Smith, John (so the outcome is SMIT-1, then SMIT-2 on second occurrence etc). The names are picked from a list on a Microsoft Form (it's an application process and we need to know who the person is applying for the service from, specifically) and when an MS Form response is received it automatically creates a new line in the table and the reference number is generated.

    Unfortunately when a new row is added to the table, upon a second occurrence of any name, it keeps changing the previous occurrence to the new number as well. For instance:

    Name Reference
    Smith, John SMIT-1
    Jones, Tom JONE-2

    becomes:

    Name Reference
    Smith, John SMIT-1
    Jones, Tom JONE-2
    Jones, Tom JONE-2

    Where it should be SMIT-1, JONE-1, then JONE-2. I have checked the formula and it is changing the value of the middle cell (where it should read R3,R3 or R4,R4 below it is reading R4,R3 and R5,R4).

    =UPPER(CONCATENATE(LEFT(R3,4),"-",COUNTIF($R$2:R4,R3)))
    =UPPER(CONCATENATE(LEFT(R4,4),"-",COUNTIF($R$2:R5,R4)))

    I have tested this on a blank Excel worksheet without any formatting (i.e. not in a table) and it seems to work fine. So the automatic creation of a new row in the table is clearly linked to the issue. But I have no clue how to fix.

    Hope all of this makes sense! Please let me know if any more info is needed

    Any help appreciated
    Attached Files Attached Files
    Last edited by casbul; 04-16-2021 at 07:50 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    Please read the yellow banner at the top of this page on how to attach a file.

  3. #3
    Registered User
    Join Date
    04-16-2021
    Location
    Exeter, England
    MS-Off Ver
    2016
    Posts
    9

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    Quote Originally Posted by JohnTopley View Post
    Please read the yellow banner at the top of this page on how to attach a file.
    Attached now

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    The cell references in the COUNTIF do not follow the row sequence: this appeared in Row 4 down

    =UPPER(CONCATENATE(LEFT($R4,4),"-",COUNTIF($R$2:R14,R4)))

    If new entries are added at the end of the table, there should not be a problem.

  5. #5
    Registered User
    Join Date
    04-16-2021
    Location
    Exeter, England
    MS-Off Ver
    2016
    Posts
    9

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    I have corrected that and it still does the same thing.

    See the attached file, where I have added a few more names at the bottom and the problem persists.

    I have noticed that clicking on the incorrect cells I get an error icon, hovering over which I get a message saying 'This cell is inconsistent with the column formula' and, it seems, an option to correct it. But keen to avoid the issue in the first place.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    In Row 14 the same issue: =UPPER(CONCATENATE(LEFT(R14,4),"-",COUNTIF($R$2:R17,R14)))

    so it appears Tables resets the COUNTIF to the "new" last row entered starting at the "previous last": row 14 was "previous" last, row 17 the "new" last.

    I don't what the answer is and I am not a fan of tables (although I don't use Excel in any serious way - the forum is a "hobby"! - so have little experience of actually using tables).

  7. #7
    Registered User
    Join Date
    04-16-2021
    Location
    Exeter, England
    MS-Off Ver
    2016
    Posts
    9

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    That's fine -- thanks for your help anyway! Stumped me as well... if I wasn't shoe horned into using a table I don't think the issue would be there.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    I looked at Tables and still cannot work out how you "anchor" the first row: it is something like ...

    COUNTIF([Name]:[@Name],[@Name])))

    How to fix the RED portion ?

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    I am equally unskilled with tables, but wonder if something along the lines of this inelegant solution may work
    =UPPER(CONCATENATE(LEFT(R2,4),"-",SUMPRODUCT(($R$2:R4=R2)*(ROW($R$2:R4)<=ROW()))))

    if row() looks at the row you are on, it excludes those below in the table

    I have a similar problem that you can not copy a formula sideways in a table and behave as you wish it to
    Last edited by davsth; 04-16-2021 at 07:05 AM.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    @davsth,
    Glad it's not just me!! I have searched to try and find an answer to the Table equivalent of $R$2 - no success.

  11. #11
    Registered User
    Join Date
    04-16-2021
    Location
    Exeter, England
    MS-Off Ver
    2016
    Posts
    9

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    davsth:

    Not going to lie - I have no clue what the changes you have made actually mean... but have replaced my formula with yours and it seems to have worked!

    I am going to run a few tests using the MS Form etc but hopefully can then mark as solved!

  12. #12
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    sumproduct is working the same as the same as countif. I have added a second criterita ROW($R$2:R4)<=ROW() which as well as matching the name (the first criteria), the row number has to be <= the row the formula is in. So where the formula has gone wrong in the example rows 15:17 are > row14 where the formula appears so will not be counted

    the conditions return a list of trues and falses
    true x true =1
    anything else true x false, false x false, false x true returns a 0.

    Add up the 1s and it is the count you are hopefully after

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    Here is the TABLE equivalent: thanks due to Glenn Kennedy for providing the answer

    =UPPER(CONCATENATE(LEFT(R2,4),"-",COUNTIF(INDEX([Name],1):INDEX([Name],ROWS($1:1)),[@Name])))

  14. #14
    Registered User
    Join Date
    04-16-2021
    Location
    Exeter, England
    MS-Off Ver
    2016
    Posts
    9

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    Thank you both so very much - both work, and have saved both for future reference in case I mess anything up!

    Not sure the difference between the two... but happy that the end result is positive

    Thanks again

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Help with CONCATENATE and COUNTIF to create automatic reference number

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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: 8
    Last Post: 04-26-2020, 12:00 PM
  2. Use CONCATENATE to create a reference formula
    By hjbarazi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2019, 11:16 AM
  3. Automatic Ruining Serial Number Against Multiple Reference in Excel
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-15-2015, 04:26 AM
  4. COUNTIF number of values associated with a reference
    By josh-mediwatch in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-16-2014, 10:18 AM
  5. Replies: 3
    Last Post: 11-29-2013, 05:33 AM
  6. Automatic unique reference number
    By J.1594 in forum Excel General
    Replies: 0
    Last Post: 11-24-2011, 09:23 AM
  7. How to cross reference and create automatic commenting
    By VoidnessMD in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-28-2010, 06:00 PM

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