Incrementally increasing ID#

[spena129]

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

 

[Hui]

Steve

Welcome to Chandoo.org Forums


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

 

[spena129]

It should increase by one, depending on how many previous "R" entries there are.

 

[Hui]

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

 

[spena129]

Excellent, Hui! Thanks a bunch. I read this blog and forum daily and really appreciate all the knowledge you provide.


All the Best