Need to find the higest alpahbetic name in a list of names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I have a list of names like Andrew, Zachary, Jeff etc and I need to find out
which one is the highest alphabetically. The max function doesn't seem to
work.

Is this possible?

Thanks

Carlo
 
there are several methods, depending on what you want to do with the data.
a simple way is top use <data><filter><Autofilter> and just select the
highest name
 
Try something like this:

With
A1:A5 containing this list
Fred
Ben
Karla
Zack
David

This formula returns the "Minimum" name, alphabetically
B1: =INDEX(A1:A5,MATCH(0,INDEX(COUNTIF(A1:A5,"<"&A1:A5),0),0))
In the above example: Ben

This formula returns the "Maximum" name, alphabetically
C1:
=INDEX(A1:A5,MATCH(MAX(INDEX(COUNTIF(A1:A5,"<"&A1:A5),0)),INDEX(COUNTIF(A1:A5,"<"&A1:A5),0),0))
In the above example: Zack

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Thank you Ron, it worked!

Ron Coderre said:
Try something like this:

With
A1:A5 containing this list
Fred
Ben
Karla
Zack
David

This formula returns the "Minimum" name, alphabetically
B1: =INDEX(A1:A5,MATCH(0,INDEX(COUNTIF(A1:A5,"<"&A1:A5),0),0))
In the above example: Ben

This formula returns the "Maximum" name, alphabetically
C1:
=INDEX(A1:A5,MATCH(MAX(INDEX(COUNTIF(A1:A5,"<"&A1:A5),0)),INDEX(COUNTIF(A1:A5,"<"&A1:A5),0),0))
In the above example: Zack

Does that help?
***********
Regards,
Ron

XL2002, WinXP
Click to expand...
 
Thank you bj.

bj said:
there are several methods, depending on what you want to do with the data.
a simple way is top use <data><filter><Autofilter> and just select the
highest name
Click to expand...
 
=INDEX(A1:A5,MATCH(MAX(CODE(UPPER(A1:A5))),CODE(UPPER(A1:A5)),0))

ctrl+shift+enter, not just enter
 
There's an issue with that formula.
It only uses the ASCII code for the first character in the cell.

That would be a problem in this list
Fred
Ben
Karla
Zack
Zane

The formula would return "Zack" instead of "Zane".
***********
Regards,
Ron

XL2002, WinXP
 
Assuming there are no numbers in the range:

For the first alphabetically:

=LOOKUP(2,1/((COUNTIF(rng,"<"&rng)=0)*(rng<>"")),rng)

For the last alphabetically:

=LOOKUP(2,1/((COUNTIF(rng,">"&rng)=0)*(rng<>"")),rng)

Biff
 
Biff,

Your "student" could have provided the formulae. <BG>

Remember our discussion on retrieving the nth element in a data set? I want to post my discovery. Please look for my post cause I always appreciate your comments.

By the way, do you know that the "grasshopper" has made more noises again?

Epinn

Assuming there are no numbers in the range:

For the first alphabetically:

=LOOKUP(2,1/((COUNTIF(rng,"<"&rng)=0)*(rng<>"")),rng)

For the last alphabetically:

=LOOKUP(2,1/((COUNTIF(rng,">"&rng)=0)*(rng<>"")),rng)

Biff
 
....appending an improbable character to the COUNTIF criteria shortens the
formula and returns the same results:

Min text value:
=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&"~")=1),A1:A5)

Max text value:
=LOOKUP(2,1/(COUNTIF(A1:A5,">"&A1:A5&"~")=0),A1:A5)

Or....use CHAR(7)...the ASCII Bell...... instead of "~" to be certain that
no cell will contain the character.

***********
Regards,
Ron

XL2002, WinXP
 
Wow! I am glad that I have so many experts to look up to. But, I am very lost now and I need help.

I have seen &"" quite a bit but I have never seen &"~" until now. Some of you may be aware that I like to experiment a lot. So, here I go again.
=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&"~")=1),A1:A5)

I took out the ~ and use a space instead and I got the same result.
I took out the ~ and did NOT leave a space i.e. "" and I got the *second* smallest value and not the min.
=LOOKUP(2,1/(COUNTIF(A1:A5,">"&A1:A5&"~")=0),A1:A5)

Regardless of whether I use "~", " " (with space) or "" (w/o space), I got the same correct result (i.e. the max).

I am going to make this more confusing for myself. Is there a connection between "~" and what Bob P. wrote previously? If not, can someone give me an explanation similar to the following so that I can understand "~"?
of the blanks. This addition on its own removes the #DIV/0! error, but will
cause the blanks to be counted as a unique item. A further addition to the formula
resolves this by testing for those blanks. Instead of dividing the array of
counts into 1 each time, adding the test creates an array of TRUE/FALSE
values to be divided by the equivalent element in the counts array. Each
blank will resolve to FALSE in the dividend array, and the count of the
blanks in the divisor array. The result of this will be 0, so the blanks do
not get counted. <<

I appreciate all the help I can get as I am very lost now.

Epinn

....appending an improbable character to the COUNTIF criteria shortens the
formula and returns the same results:

Min text value:
=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&"~")=1),A1:A5)

Max text value:
=LOOKUP(2,1/(COUNTIF(A1:A5,">"&A1:A5&"~")=0),A1:A5)

Or....use CHAR(7)...the ASCII Bell...... instead of "~" to be certain that
no cell will contain the character.

***********
Regards,
Ron

XL2002, WinXP
 
I should start my own thread. Please move over to the thread with subject. &"" or &" " or &"~"

Sorry for the inconvenience.

Epinn

Wow! I am glad that I have so many experts to look up to. But, I am very lost now and I need help.

I have seen &"" quite a bit but I have never seen &"~" until now. Some of you may be aware that I like to experiment a lot. So, here I go again.
=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&"~")=1),A1:A5)

I took out the ~ and use a space instead and I got the same result.
I took out the ~ and did NOT leave a space i.e. "" and I got the *second* smallest value and not the min.
=LOOKUP(2,1/(COUNTIF(A1:A5,">"&A1:A5&"~")=0),A1:A5)

Regardless of whether I use "~", " " (with space) or "" (w/o space), I got the same correct result (i.e. the max).

I am going to make this more confusing for myself. Is there a connection between "~" and what Bob P. wrote previously? If not, can someone give me an explanation similar to the following so that I can understand "~"?
of the blanks. This addition on its own removes the #DIV/0! error, but will
cause the blanks to be counted as a unique item. A further addition to the formula
resolves this by testing for those blanks. Instead of dividing the array of
counts into 1 each time, adding the test creates an array of TRUE/FALSE
values to be divided by the equivalent element in the counts array. Each
blank will resolve to FALSE in the dividend array, and the count of the
blanks in the divisor array. The result of this will be 0, so the blanks do
not get counted. <<

I appreciate all the help I can get as I am very lost now.

Epinn

....appending an improbable character to the COUNTIF criteria shortens the
formula and returns the same results:

Min text value:
=LOOKUP(2,1/(COUNTIF(A1:A5,"<"&A1:A5&"~")=1),A1:A5)

Max text value:
=LOOKUP(2,1/(COUNTIF(A1:A5,">"&A1:A5&"~")=0),A1:A5)

Or....use CHAR(7)...the ASCII Bell...... instead of "~" to be certain that
no cell will contain the character.

***********
Regards,
Ron

XL2002, WinXP
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula to find highest alphabetic name in a list of names 6
select max value from row of data and column containing max value 1
Set non-continuous PrintArea via Function / Name 0
VBA - retrieve data on last create named range from previous inserted named range 0
Referencing text of a cell next to the greatest value in column 1
Is there a function to give us the highest value in a list of tex 8
I'm useing Excel I have a list of names that I need alphabetical 2
Can excel alphabetize a list of names? 1

Back
Top