Hi,

I wonder if anyone can help me. I've tried to use the max function but it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and I
want to find Zachary for example as its the last name alphabetically in an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S

Let's say all the last name in A2:A100
Create a helper column B
In B2 =Code(A2)
Copy down to B100

In C2 =INDEX(A2:A100,MATCH(MAX(B2:B100),B2:B100,0))


"S" wrote:

Hi,

I wonder if anyone can help me. I've tried to use the max function but it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and I
want to find Zachary for example as its the last name alphabetically in an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S

=INDEX(H:H,MATCH("Zachary",H:H,0))

--
Don Guillett
SalesAid Software

"S" wrote in message
...
Hi,

I wonder if anyone can help me. I've tried to use the max function but it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and I
want to find Zachary for example as its the last name alphabetically in an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S

S,

You'll need to use a helper column of formulas.

Assuming that your list of names starts in cell A2 and continues down column A to cell A100, in cell
B2 use the formula

=IF(SUMPRODUCT((A2<$A$2:$A$100)*1)=0,"Highest","")

and copy down to cell B100.

Then use the formula

=INDEX(A:A,MATCH("Highest",B:B,FALSE))

to pull the name.

HTH,
Bernie
MS Excel MVP


"S" wrote in message
...
Hi,

I wonder if anyone can help me. I've tried to use the max function but it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and I
want to find Zachary for example as its the last name alphabetically in an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S

Hello,

Try a helper column:
If your names are in cells A1:A999, then enter into B1:
=COUNTIF($A$1:$A$999,""&A1)+COUNTIF($A$1:$A1,A1)
and copy down to B999.
Now search for value 1 in B1:B999. The corresponding cell in column A
is your alphabetic max.

Please notice that if you have two or more identical alphabetic
maximums, then value 1 will be shown only for the occurence with the
lowest row number (omit the second COUNTIF and look for value 0 if you
need to see all occurrences of the max).

HTH,
Bernd

Try this:

Array entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

If there will *NEVER* be any empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(COUNTIF(A$1:A$20,"<"&A $1:A$20),1),COUNTIF(A$1:A$20,"<"&A$1:A$20),0))

If there may be empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(IF(A$1:A$20<"",COUNTI F(A$1:A$20,"<"&A$1:A$20)),1),IF(A$1:A$20<"",COUNT IF(A$1:A$20,"<"&A$1:A$20)),0))

Biff

"S" wrote in message
...
Hi,

I wonder if anyone can help me. I've tried to use the max function but it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and I
want to find Zachary for example as its the last name alphabetically in an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S

Tammy
Trish
Tim
Tony

Which one is the "highest"?

Biff

"Teethless mama" wrote in message
...
Let's say all the last name in A2:A100
Create a helper column B
In B2 =Code(A2)
Copy down to B100

In C2 =INDEX(A2:A100,MATCH(MAX(B2:B100),B2:B100,0))


"S" wrote:

Hi,

I wonder if anyone can help me. I've tried to use the max function but it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and I
want to find Zachary for example as its the last name alphabetically in
an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S

Or maybe:

=INDEX($A$1:$A$20,MATCH(0,COUNTIF($A$1:$A$20,""&$ A$1:$A$20),0))

Again array entered (ie CSE).

Richard





T. Valko wrote:
Try this:

Array entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

If there will *NEVER* be any empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(COUNTIF(A$1:A$20,"<"&A $1:A$20),1),COUNTIF(A$1:A$20,"<"&A$1:A$20),0))

If there may be empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(IF(A$1:A$20<"",COUNTI F(A$1:A$20,"<"&A$1:A$20)),1),IF(A$1:A$20<"",COUNT IF(A$1:A$20,"<"&A$1:A$20)),0))

Biff

"S" wrote in message
...
Hi,

I wonder if anyone can help me. I've tried to use the max function but it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and I
want to find Zachary for example as its the last name alphabetically in an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S

Yeah, that'll work. And to account for possible empty cells and/or formula
blanks:

=INDEX(A1:A20,MATCH(0,IF(A1:A20<"",COUNTIF(A1:A20 ,""&A1:A20)),0))

Biff

"RichardSchollar" wrote in message
ups.com...
Or maybe:

=INDEX($A$1:$A$20,MATCH(0,COUNTIF($A$1:$A$20,""&$ A$1:$A$20),0))

Again array entered (ie CSE).

Richard





T. Valko wrote:
Try this:

Array entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

If there will *NEVER* be any empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(COUNTIF(A$1:A$20,"<"&A $1:A$20),1),COUNTIF(A$1:A$20,"<"&A$1:A$20),0))

If there may be empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(IF(A$1:A$20<"",COUNTI F(A$1:A$20,"<"&A$1:A$20)),1),IF(A$1:A$20<"",COUNT IF(A$1:A$20,"<"&A$1:A$20)),0))

Biff

"S" wrote in message
...
Hi,

I wonder if anyone can help me. I've tried to use the max function but
it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and
I
want to find Zachary for example as its the last name alphabetically in
an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S

This is fun. The following also works (and doesn't need to be array
entered):

=LOOKUP(2,1/((COUNTIF($A$1:$A$20,""&$A$1:$A$20)=0)*($A$1:$A$2 0<"")),$A$1:$A$20)

Richard


T. Valko wrote:
Yeah, that'll work. And to account for possible empty cells and/or formula
blanks:

=INDEX(A1:A20,MATCH(0,IF(A1:A20<"",COUNTIF(A1:A20 ,""&A1:A20)),0))

Biff

"RichardSchollar" wrote in message
ups.com...
Or maybe:

=INDEX($A$1:$A$20,MATCH(0,COUNTIF($A$1:$A$20,""&$ A$1:$A$20),0))

Again array entered (ie CSE).

Richard





T. Valko wrote:
Try this:

Array entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

If there will *NEVER* be any empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(COUNTIF(A$1:A$20,"<"&A $1:A$20),1),COUNTIF(A$1:A$20,"<"&A$1:A$20),0))

If there may be empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(IF(A$1:A$20<"",COUNTI F(A$1:A$20,"<"&A$1:A$20)),1),IF(A$1:A$20<"",COUNT IF(A$1:A$20,"<"&A$1:A$20)),0))

Biff

"S" wrote in message
...
Hi,

I wonder if anyone can help me. I've tried to use the max function but
it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and
I
want to find Zachary for example as its the last name alphabetically in
an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S

I wonder if that's any faster (calculation-wise) vs the array version?

Biff

"RichardSchollar" wrote in message
oups.com...
This is fun. The following also works (and doesn't need to be array
entered):

=LOOKUP(2,1/((COUNTIF($A$1:$A$20,""&$A$1:$A$20)=0)*($A$1:$A$2 0<"")),$A$1:$A$20)

Richard


T. Valko wrote:
Yeah, that'll work. And to account for possible empty cells and/or
formula
blanks:

=INDEX(A1:A20,MATCH(0,IF(A1:A20<"",COUNTIF(A1:A20 ,""&A1:A20)),0))

Biff

"RichardSchollar" wrote in message
ups.com...
Or maybe:

=INDEX($A$1:$A$20,MATCH(0,COUNTIF($A$1:$A$20,""&$ A$1:$A$20),0))

Again array entered (ie CSE).

Richard





T. Valko wrote:
Try this:

Array entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

If there will *NEVER* be any empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(COUNTIF(A$1:A$20,"<"&A $1:A$20),1),COUNTIF(A$1:A$20,"<"&A$1:A$20),0))

If there may be empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(IF(A$1:A$20<"",COUNTI F(A$1:A$20,"<"&A$1:A$20)),1),IF(A$1:A$20<"",COUNT IF(A$1:A$20,"<"&A$1:A$20)),0))

Biff

"S" wrote in message
...
Hi,

I wonder if anyone can help me. I've tried to use the max function
but
it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names
and
I
want to find Zachary for example as its the last name alphabetically
in
an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S