Sequential numbering (2 different numbers?)

1. 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. 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. 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.

4. 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. 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?

6. Re: Sequential numbering (2 different numbers?)

You're welcome - glad to help.

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

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