Formula to return highest string snippet

[fairlanecobra460]

Im a bit lost on how to approach this. I have a list of purchase order confirmations and I want to return a "this one" for each where the trailing text is max:

P0001-1
P0001-2 this one
P0002-1 this one
P0003-1 this one
P0004-1 this one
P0005-1
P0005-2 this one

The formula needs to be automatic (im currently using remove duplicates functionality)
Any ideas would be appreciated.

[FDibbins]

Hi, welcome to the forum

Probably the simplest way to do this would be to use use a helper column and break out the numbers, then find the highest of those. 1 way to do that would be Text2Columns

[MarvinP]

Hi cobra and welcome to the forum,

See the attached that takes two columns to fill in. Instead of a simple formula I've added a Dynamic Named Range and based a Pivot off it. Then some VBA code to auto update anything that is added or updated in columns A or B.

On Change PT Max.xlsm

[AlKey]

Here is another way. This will require two helper columns

in B2 and copy down

Please Login or Register  to view this content.

in C2 and copy down

Please Login or Register  to view this content.

in D2 and copy down (to get unique PO numbers)

Please Login or Register  to view this content.

In E2 and copy down ( for results)

Please Login or Register  to view this content.

v	A	B	C	D	E
1	Data	Helper 1	Helper 2	Results	Vaues
2	P0001-1	P0001	1	P0001	2
3	P0001-2	P0001	2	P0002	1
4	P0002-1	P0002	1	P0003	1
5	P0003-1	P0003	1	P0004	1
6	P0004-1	P0004	1	P0005	2
7	P0005-1	P0005	1
8	P0005-2	P0005	2

[fairlanecobra460]

AlKey, this is exactly what Im looking for, thank you.
Marvin, thanks as well.