• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Incrementally increasing ID#

spena129

New Member
Hi All,

I need help! I have a data table and I would like a unique ID# assigned to each new entry based on 3 criteria. I have already constructed the 1st two parts of the ID#, but cannot figure out how to increase the number based on previous entries.


Example:


First part of ID# extracted from date: 12/1/2010 = "10"


Column 1 is a date. From that date, I extract the last 2 digits of the year.


Second part of ID# extracted from data entry: True = "H"

Column 2 has two choices. Depending on choice, I assign either "H" or "R" to ID#.


Last part of ID#: 6 True = "6"


Based on a count of choice (see part 2), increase ending digit by 1.


For the above example, the new ID# would be "10H6" because the date entry was in year 2010, the choice was "True", and there were 5 previous "True" choices in the table.


Any thoughts? Let me know if you need more info!


Thanks in advance


Steve
 
Steve

Welcome to Chandoo.org Forums


What happens to the last part if the second part is R not H?
 
Spena129

Assumining your Dates are in Column A and True/False in Column B

Then in C2 you could use: =TEXT(A2,"yy")&IF(B2,"H","R")&COUNTIF($B$2:B2,IF(B2,TRUE,FALSE))

Copy down


The bit you want is the COUNTIF($B$2:B2,IF(B2,TRUE,FALSE)) bit
 
Excellent, Hui! Thanks a bunch. I read this blog and forum daily and really appreciate all the knowledge you provide.


All the Best
 
Back
Top