Finding Largest value

[skittlz]

I have "Asset ID" in Column A, and "Unit ID" in Column B.

See Image of sample data below.

I need to find the largest unit ID for a given Asset.

I found:

MaxSN = Application.WorksheetFunction.Max(Range("B:B"))

But this only works if there are numbers - the SN messes it up. I tried using OnlyDigits function to get just the numbers, but that didn't work.

I also need the MaxSN for the specific Asset = for example, E-027 should result in "3", E-017 should result in 10, and so on.

How do i get the Largest SN for a specified Asset?

 

[DanteAmor]

Try:

Libro1
	A	B	C	D	E
1	Asset ID	Unit ID
2	E-017	SN001		E-017	10
3	E-017	SN010		E-027	3
4	E-017	SN002
5	E-017	SN003
6	E-027	SN02
7	E-027	SN003
Hoja2

Cell Formulas
Range	Formula
E2:E3	E2	=MAX(($A$2:$A$7=D2)*(SUBSTITUTE($B$2:$B$7,"SN","")))
Press CTRL+SHIFT+ENTER to enter array formulas.

In excel 365, it is not necessary to enter the formula with CTRL+SHIFT+ENTER

 

[DanteAmor]

If you are including a larger range, where there may be empty cells or in column B they do not have the prefix "SN":

=MAX(IFERROR(($A$2:$A$100=D3)*(SUSTITUIR($B$2:$B$100,"SN",0)),0))