Highest value in an alphabetical list

emte69

New Member
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?

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Post a better sample along with the desired results.

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

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)

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
ABCD
1
2DEP_HW0001DEP_HW
3DEP_HW0000DEP_HW0004
4DEP_HW0003
5DEP_HW0004
6DEP_SW0001
7DEP_SW0002
8DEP_SW0003
Sheet3

emte69 said:
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
ABCDE
12
2Item0DistinctItemSeedsMaxMax-valuedItem
3DEP_HW00011DEP_HW4DEP_HW0004
4DEP_HW0002 DEP_SW3DEP_SW0003
5DEP_HW0003
6DEP_HW0004
7DEP_SW00012
8DEP_SW0002
9DEP_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.

Replies
8
Views
206
Replies
6
Views
230
Replies
23
Views
356
Replies
4
Views
130
Replies
2
Views
167

1,203,690
Messages
6,056,755
Members
444,889
Latest member
ibbara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back