Highest value in an alphabetical list

[emte69]

Hi folks
I have a list with values like

xyz_abcd_jk0001
............._jk0002
............._jk0003
............._nm0001

I want to show in a cell the alphabetical "highest" item of only the xyz_abcd_jk values in the list (in the example: xyz_abcd_jk003)
Does anybody has an idea?
Thx in advance

 

[Aladin Akyurek]

Post a better sample along with the desired results.

 

[emte69]

More realistic sample:

DEP_HW0001
DEP_HW0002
DEP_HW0003
DEP_HW0004
DEP_SW0001
DEP_SW0002
DEP_SW0003

Data pattern 3 letter - underscore - 2 letter 4 digit number

 

[RobShaw]

Do you want to identify the highest letter in the text part of the value or do you mean the highest value in the table

i.e.
Do you want...
ABC_B output C
ABC_C output C
ABC_D output D

or....
ABC_B
ABC_C
ABC_D

output ABC_D as the highest alphabetical value (I think ascii values increase towards Z (Zis the highest value)

please clarify

 

[fairwinds]

Hi,

Maybe like this?

=INDEX(A2:A8,MATCH(MAX(COUNTIF(A2:A8,"<"&A2:A8)*(LEFT(A2:A8,6)=C2)),COUNTIF(A2:A8,"<"&A2:A8)*(LEFT(A2:A8,6)=C2)),0)
must be confirmed with Ctrl + Shift + Enter.

Book1
	A	B	C	D
1
2	DEP_HW0001		DEP_HW
3	DEP_HW0000		DEP_HW0004
4	DEP_HW0003
5	DEP_HW0004
6	DEP_SW0001
7	DEP_SW0002
8	DEP_SW0003
Sheet3

 

[Aladin Akyurek]

More realistic sample:

DEP_HW0001
DEP_HW0002
DEP_HW0003
DEP_HW0004
DEP_SW0001
DEP_SW0002
DEP_SW0003

Data pattern 3 letter - underscore - 2 letter 4 digit number

Thanks, but you alas omitted the desired result(s).

Book1
	A	B	C	D	E
1			2
2	Item	0	DistinctItemSeeds	Max	Max-valuedItem
3	DEP_HW0001	1	DEP_HW	4	DEP_HW0004
4	DEP_HW0002		DEP_SW	3	DEP_SW0003
5	DEP_HW0003
6	DEP_HW0004
7	DEP_SW0001	2
8	DEP_SW0002
9	DEP_SW0003
10
Sheet1

Formulas...

B2 must house a 0.

B3, copied down:

=IF((A3<>"")*ISNUMBER(MATCH(LEFT(A3,6)&"*",$A$2:A2,0)),"",LOOKUP(9.99999999999999E+307,$B$2:B2)+1)

C1:

=LOOKUP(9.99999999999999E+307,B:B)

C3, copied down:

=IF(ROW()-ROW(C$3)+1<=$C$1,LEFT(LOOKUP(ROW()-ROW(C$3)+1,B:B,A:A),6),"")

D3: (a possible desired result)

=IF(C3<>"",MAX(IF(LEFT($A$3:$A$9,6)=C3,--REPLACE($A$3:$A$9,1,6,""))),"")

which must be confirmed with control+shift+enter and then copied down.

E3: (an alternative desired result)

=IF(C3<>"",C3&TEXT(MAX(IF(LEFT($A$3:$A$9,6)=C3,--REPLACE($A$3:$A$9,1,6,""))),"0000"),"")

which must be confirmed with control+shift+enter and then copied down.