+ Reply to Thread
Results 1 to 6 of 6

Sequential numbering (2 different numbers?)

  1. #1
    Registered User
    Join Date
    11-17-2020
    Location
    Frankfort, KY
    MS-Off Ver
    365, 2013
    Posts
    6

    Sequential numbering (2 different numbers?)

    Hi, I don't know if this is even possible, but I'll try to make this as clear as I can:

    In our lab, our sample numbers are in this format: yymmddPT##, where PT stands for plant (in this particular example), and the ## is the number of that sample for that day. We always have a PT01 and a PT02, at least. So the numbers from today would be 201117PT01 and 201117PT02. I want to know if there is a formula that can automatically populate these numbers based on the date entered? See attached image for what I'm looking at.

    Thanks for any help in advance!!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Sequential numbering (2 different numbers?)

    I can't see an attached image, but it would be better to attach a sample Excel workbook anyway, so that we can try out different approaches in it before getting back to you. See the yellow banner at the top of the screen for details of how to attach a workbook to one of your posts.

    Pete

  3. #3
    Registered User
    Join Date
    11-17-2020
    Location
    Frankfort, KY
    MS-Off Ver
    365, 2013
    Posts
    6

    Re: Sequential numbering (2 different numbers?)

    Ok, I'm attaching it now. Sorry about that. I don't have any formulas or anything on there right now, just a layout. But hopefully you can see where I'm going with it.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Sequential numbering (2 different numbers?)

    You can use this formula in C2:

    =IF(C$1="","",TEXT(C$1,"yymmddPT")&TEXT(ROWS($1:1),"00"))

    then copy across and down as required.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    11-17-2020
    Location
    Frankfort, KY
    MS-Off Ver
    365, 2013
    Posts
    6

    Re: Sequential numbering (2 different numbers?)

    That worked. Thanks so much!!

    So I'm not a super-advanced excel user, so I apologize if this is a dumb question. But could you explain the breakdown of that formula to me?
    Last edited by NiesahClark; 11-24-2020 at 12:04 PM. Reason: after thought

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Sequential numbering (2 different numbers?)

    You're welcome - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    There are basically 3 parts to the formula. The IF part just looks to see if the cell in column C is empty, and if so it returns an empty cell.

    If column C is not empty, it is assumed to contain a date, and the first TEXT part of the formula just formats this date as yymmdd and adds "PT" onto the end of that.

    Also added on to the end is a two digit number. The ROWS($1:1) part initially returns the value 1, and so the TEXT function around it forces it into a 2-digit number, i.e. 01. But, when the formula is copied down the ROWS term becomes ROWS($1:2), which will return 2 and this in turn becomes 02, then on the next row it becomes ROWS($1:3), thus returning 03, and so on. So this part of the formula produces an incrementing 2-digit number as it is copied down.

    Hope this helps.

    Pete

+ 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. [SOLVED] Non-sequential row numbering
    By joelh327 in forum Excel General
    Replies: 5
    Last Post: 01-27-2016, 01:04 AM
  2. Replies: 12
    Last Post: 12-16-2014, 11:11 AM
  3. [SOLVED] re-numbering a list of numbers based on another list of numbers to get a sequential order
    By ryan.wherry in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-10-2014, 03:27 PM
  4. [SOLVED] Sequential Numbering
    By Filtec in forum Excel General
    Replies: 7
    Last Post: 04-15-2013, 04:01 AM
  5. Numbering a sequential list, but exclude a list of given numbers.
    By Fiebuls in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-12-2012, 03:29 AM
  6. Sequential Numbering
    By justin68 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-27-2007, 02:17 AM
  7. Sequential Numbering
    By Mel in forum Excel General
    Replies: 10
    Last Post: 05-09-2006, 10:15 AM

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