Unique ID based on Next Column

[Austin Lang]

Hello friends,

Back again with another one..

Any assistance in throwing this together would be appreciated. The goal here is to have Column A here generate an ID only if Column B shows "Y". The ID needs to be formatted "24-XX". We also need it to be sequential from the last number generated. In this example, I would be looking at "24-13".

Thank you in advance.

 

[DanteAmor]

Try:

Dante Amor
	A	B
2	24-10	Y
3	24-11	Y
4	24-12	Y
5	N/A	N
6	24-13	Y
Hoja6

Cell Formulas
Range	Formula
A3:A6	A3	=IF(B3="Y","24-"&MAX(IF(B$2:B2="Y",RIGHT(A$2:A2,2)+1)),"N/A")
Press CTRL+SHIFT+ENTER to enter array formulas.

You must enter the first consecutive one manually, in this case 24-10.


If the consecutive has more than 2 digits:

Dante Amor
	A	B
2	24-10	Y
3	24-11	Y
4	24-12	Y
5	N/A	N
6	24-13	Y
Hoja6

Cell Formulas
Range	Formula
A3:A6	A3	=IF(B3="Y","24-"&MAX(IF(B$2:B2="Y",MID(A$2:A2,FIND("-",A$2:A2)+1,9)+1)),"N/A")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Austin Lang]

Since the number will eventually hit triple digits, I just started with the 2nd formula right from the start.

Thank you so much for the help!

 

[Peter_SSs]

Since the number will eventually hit triple digits,

Here is a shorter one that you could try too. I also don't think that it should require Ctrl+Shift+Enter with your versions.

24 01 06.xlsm
	A	B
1
2	24-10	Y
3	24-11	Y
4	24-12	Y
5	N/A	N
6	24-13	Y
ID

Cell Formulas
Range	Formula
A3:A6	A3	=IF(B3="Y",24&LOOKUP(1,-MID(A$2:A2,4,9))-1,"N/A")