Latest Part Revision

[rmhansen]

I have a list of parts with revisions such as below, all in one cell:
ABCD - 0
ABCD - 1
EFGH - 23
EFGH - 24
IJKL - 7
MNOP -

I need a formula to show me the "maximum" or latest revision number for each part. So result in one cell would be:
ABCD - 1
EFGH - 24
IJKL - 7
MNOP -

Thank you!!

 

[etaf]

what does just a - mean , high or low,
MNOP -
higher than MNOP - 7 or lower

What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available

 

[rmhansen]

what does just a - mean , high or low,
MNOP -
higher than MNOP - 7 or lower

What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available

Thanks for the quick response! I'll update my profile. Version: 365 MSO (Version 2208 Build 16.0.15601.20676) 32-bit

The hyphen is just how the data is formatted. It lists the part number number, followed by a hyphen, and then the rev number if there is one. Some have revisions 1-10, some only list the latest 2 revisions, and some have no revisions at all which is what I was trying to show with the "MNOP - " example. If there is a part with a blank and a number, the number is higher.
So if both "MNOP - 7" & "MNOP - " are listed, I would want "MNOP - 7" to be the output.

 

[FormR]

all in one cell

Hi, do you mean like this?

Book4
	A	B
1	ABCD - 0 ABCD - 1 EFGH - 23 EFGH - 24 IJKL - 7 MNOP -	ABCD - 1 EFGH - 24 IJKL - 7 MNOP -
Sheet1

Or is it more like this?

Book5
	A	B
1	ABCD - 0	ABCD - 1
2	ABCD - 1	EFGH - 24
3	EFGH - 23	IJKL - 7
4	EFGH - 24	MNOP -
5	IJKL - 7
6	MNOP -
Sheet1

 

[rmhansen]

Hi, do you mean like this?

Book4
	A	B
1	ABCD - 0 ABCD - 1 EFGH - 23 EFGH - 24 IJKL - 7 MNOP -	ABCD - 1 EFGH - 24 IJKL - 7 MNOP -
Sheet1

Or is it more like this?

Book5
	A	B
1	ABCD - 0	ABCD - 1
2	ABCD - 1	EFGH - 24
3	EFGH - 23	IJKL - 7
4	EFGH - 24	MNOP -
5	IJKL - 7
6	MNOP -
Sheet1

How you have it in book5 would be perfect!

 

[FormR]

Hi, then you could give this a try:

Book1
	A	B
1	ABCD - 0	ABCD - 1
2	ABCD - 1	EFGH - 24
3	EFGH - 23	IJKL - 7
4	EFGH - 24	MNOP -
5	IJKL - 7
6	MNOP -
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=LET(PT,TEXTBEFORE(A1:A6," -"),RV,TEXTAFTER(A1:A6,"- "),UN,UNIQUE(PT),UN&" - "&BYROW(UN,LAMBDA(BR,IFERROR(MAX(FILTER(0+RV,PT=BR)),""))))
Dynamic array formulas.