Scan column for next available number

[rplohocky]

Hello,
I have a column full of titles that look like this:

Ped 3081 CAB 3 Ped 3082 CAB 3 Ped 3083 CAB 3 Ped 3084 CAB 3 Ped 3085 CAB 3 PED 3086 CAB 3 PED 3104 CAB 4 PED 3105 CAB 4 PED 3106 CAB 4 PED 3107 CAB 4 PED 3108 CAB 4 PED 3109 CAB 4 PED 3110 CAB 4

I am looking for a formula that will search through column A looking for a specific set of words such as "CAB 3" or "CAB 4" then look at the number just after the word "PED" and provide the next available number i can use to name the next "PED". So if, in this case, "PED 3110 CAB 4" the formula would only look in the "CAB 4" 's and return the next available number which would be "3111". I can create a separate cell for each search if need be. I started with this but it does not work:
=IF(ISNUMBER(SEARCH("CAB 3",A:A)),IFERROR(INDEX(ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))),MATCH(0,INDEX(COUNTIF(A:A,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))),),0)),MAX(A:A)+1),"")

Thanks in advance!

 

[jasonb75]

This looks like it works.

Book1
	A	B	C
2	Ped 3081 CAB 3		CAB 4
3	Ped 3082 CAB 3		3111
4	Ped 3083 CAB 3
5	Ped 3084 CAB 3
6	Ped 3085 CAB 3
7	PED 3086 CAB 3
8	PED 3104 CAB 4
9	PED 3105 CAB 4
10	PED 3106 CAB 4
11	PED 3107 CAB 4
12	PED 3108 CAB 4
13	PED 3109 CAB 4
14	PED 3110 CAB 4
Sheet1

Cell Formulas
Range	Formula
C3	C3	=LET(l,--MID(FILTER(A2:A14,ISNUMBER(SEARCH(C2,A2:A14)),""),5,4),s,SEQUENCE(MAX(l)-MIN(l)+2,,MIN(l)),MIN(IF(ISNA(MATCH(s,l,0)),s)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rplohocky]

This looks like it works.

Book1
	A	B	C
2	Ped 3081 CAB 3		CAB 4
3	Ped 3082 CAB 3		3111
4	Ped 3083 CAB 3
5	Ped 3084 CAB 3
6	Ped 3085 CAB 3
7	PED 3086 CAB 3
8	PED 3104 CAB 4
9	PED 3105 CAB 4
10	PED 3106 CAB 4
11	PED 3107 CAB 4
12	PED 3108 CAB 4
13	PED 3109 CAB 4
14	PED 3110 CAB 4
Sheet1

Cell Formulas
Range	Formula
C3	C3	=LET(l,--MID(FILTER(A2:A14,ISNUMBER(SEARCH(C2,A2:A14)),""),5,4),s,SEQUENCE(MAX(l)-MIN(l)+2,,MIN(l)),MIN(IF(ISNA(MATCH(s,l,0)),s)))

Thank you jasonb75! It work exactly like I want it too! It will save me a lot of time and guessing! I also updated my version of Excel since it was from 2005.