Trying to find if a number exists in a column and +1 it

[NORRILLOUS]

I have been trying to get a formula to work.

the logic I am trying to achieve is something like "If my serial number is found in my range then add "+1" to the end digit of the string."

First I figured out how to tell if the result of a formula already exists in a column. The Formula below returns True.

=ISNUMBER(MATCH(A8&"-"&D8&"."&LEFT(E8,1)&".0",$F$2:$F$1000,0))

The problem is I can have multiple serial numbers show as the same but I want them to increase as more is added on the same date. So I added the above into an if statement and added +1 but I am stuck here. It will Show the serial number with .0 to start and the second will be .1 but the third is null.

=IF(ISNUMBER(MATCH(A8&"-"&D8&"."&LEFT(E8,1)&".0",$F$2:$F$1000,0)),A8&"-"&D8&"."&LEFT(E8,1)&"."&"0"+1,)

I appreciate any help and Thank you in advance.

 

[J.Ty.]

Could you please demonstrate a few examples of your serial numbers: the initial one you are searching for, and the one you want to get. Please cover corner cases: e.g. what happens if the last digit is 9.

J.Ty.

 

[NORRILLOUS]

Yes Below are a few values of the serial numbers. Every serial number when created I have set to end in 0. and I would like to count up incrementally from there if more than one serial number is assigned on that date and shift.

1S-43893.2.0

2P-43895.3.0

5P-43895.1.0

For example:

If there were 3 action items assigned in the same area and the resulting serial number was 5P-43895.1.0 I would need the new resulting serial number found in my list to increase the final digit by 1.

5P-43895.1.0

5P-43895.1.1

5P-43895.1.2

Now if the resulting serial number goes beyond 5P-43895.1.9 it is fine for it to be 5P-43895.1.10, 5P-43895.1.11, 5P-43895.1.12 etc...

 

[J.Ty.]

I have made the following. Please test it.

Book1
	A	B	C	D	E	F
1	5P-43895.1.3
2						5P-43895.1.0
3						5P-43895.1.1
4						5P-43895.1.2
5
6
7
8	5P			43895	1
Sheet1

Cell Formulas
Range	Formula
A1	A1	=A8&"-"&D8&"."&LEFT(E8,1)&"."&(MAX(VALUE(RIGHT(F2:F4,LEN(F2:F4)-LEN(A8&"-"&D8&".")-2)))+1)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[jasonb75]

Is this what you need?

Sample Data.xlsm
	A	B	C	D	E	F
1						Serial numbers
2	1S			05/03/2020	1	1S-43895.1.0
3	2P			05/03/2020	1	2P-43895.1.0
4	1S			05/03/2020	1	1S-43895.1.1
5	1S			05/03/2020	1	1S-43895.1.2
6	2P			05/03/2020	1	2P-43895.1.1
7	1S			06/03/2020	1	1S-43896.1.0
8	2P			05/03/2020	1	2P-43895.1.2
Sheet10

Cell Formulas
Range	Formula
F2:F8	F2	=A2&"-"&D2&"."&LEFT(E2,1)&"."&COUNTIF(F$1:F1,A2&"-"&D2&"."&LEFT(E2,1)&".*")

 

[NORRILLOUS]

Thank you jasonb75 & J.Ty. for your responses!

I found that jasonb75's response fit into my sheet well and worked perfectly!