Compare String and Return Value

[Pestomania]

I have been trying to identify how to compare a string and return if it is latest version. I have a large amount of data that looks like:

I have a column of "data" that has a letter at the end of the string. If it is a letter, the largest letter (F>A) is the latest revision. If there is a data point with no letter, but the precursor has a letter, it is obsoleted. (i.e. Route 921 and Route 921 A. Route 921 A is the latest and Route 921 is obsolete.)

Sage Tables Created 2023-09-19.xlsx
	AH	AI
1	Data	Latest Rev?
4	Route 1234 A	No
5	Route 1234 B	Yes
9	Route 1234	No
10	Route 5567 D	No
14	Route 5567 F	Yes
15	Route 921 A	No
16	Route 921	No
17	Route 921 F	Yes
FMI - Route Tool Code Upload 9-

 

[Pestomania]

Will the highest letter for any Route be toward the bottom of the list or are they mixed up?

They are all mixed up.

 

[Pestomania]

I have been trying to identify how to compare a string and return if it is latest version. I have a large amount of data that looks like:

I have a column of "data" that has a letter at the end of the string. If it is a letter, the largest letter (F>A) is the latest revision. If there is a data point with no letter, but the precursor has a letter, it is obsoleted. (i.e. Route 921 and Route 921 A. Route 921 A is the latest and Route 921 is obsolete.)

Sage Tables Created 2023-09-19.xlsx
	AH	AI
1	Data	Latest Rev?
4	Route 1234 A	No
5	Route 1234 B	Yes
9	Route 1234	No
10	Route 5567 D	No
14	Route 5567 F	Yes
15	Route 921 A	No
16	Route 921	No
17	Route 921 F	Yes
FMI - Route Tool Code Upload 9-

I just noticed, I provided backwards information.

If it does not have a letter, it is a higher revision (more recent).

 

[rlv01]

If it does not have a letter, it is a higher revision (more recent).

In that case:

Book1
	A	B
1	Data	Latest Rev?
2	Route 1234 A	No
3	Route 1234 B	No
4	Route 1234	Yes
5	Route 5567 D	No
6	Route 5567 F	No
7	Route 921 A	No
8	Route 921	Yes
9	Route 921 F	No
Sheet2

Cell Formulas
Range	Formula
B2:B9	B2	=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"Yes","No")

 

[Scott Huish]

Also:

Book2
	A	B
1	Data	Latest Rev?
2	Route 1234 A	No
3	Route 1234 B	No
4	Route 1234	Yes
5	Route 5567 D	No
6	Route 5567 F	No
7	Route 921 A	No
8	Route 921	Yes
9	Route 921 F	No
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=IF(ISNUMBER(--RIGHT(A2)),"Yes","No")