Need to find the higest alpahbetic name in a list of names
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 |
Need to find the higest alpahbetic name in a list of names
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 "Carlo" wrote: 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 |
Need to find the higest alpahbetic name in a list of names
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 "Carlo" wrote: 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 |
Need to find the higest alpahbetic name in a list of names
Thank you Ron, it worked!
"Ron Coderre" wrote: 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 "Carlo" wrote: 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 |
Need to find the higest alpahbetic name in a list of names
Thank you bj.
"bj" wrote: 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 "Carlo" wrote: 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 |
Need to find the higest alpahbetic name in a list of names
=INDEX(A1:A5,MATCH(MAX(CODE(UPPER(A1:A5))),CODE(UP PER(A1:A5)),0))
ctrl+shift+enter, not just enter "Carlo" wrote: 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 |
Need to find the higest alpahbetic name in a list of names
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 "Teethless mama" wrote: =INDEX(A1:A5,MATCH(MAX(CODE(UPPER(A1:A5))),CODE(UP PER(A1:A5)),0)) ctrl+shift+enter, not just enter "Carlo" wrote: 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 |
Need to find the higest alpahbetic name in a list of names
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 "Carlo" wrote in message ... 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 |
Need to find the higest alpahbetic name in a list of names
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 "T. Valko" wrote in message ... 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 "Carlo" wrote in message ... 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 |
Need to find the higest alpahbetic name in a list of names
....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 "T. Valko" wrote: 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 "Carlo" wrote in message ... 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 |
Need to find the higest alpahbetic name in a list of names
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. Min text value: =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. Max text value: =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 "~"? Adding &"" to the end of the COUNTIF formula forces a count 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 "Ron Coderre" wrote in message ... ....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 "T. Valko" wrote: 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 "Carlo" wrote in message ... 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 |
Need to find the higest alpahbetic name in a list of names
I should start my own thread. Please move over to the thread with subject. &"" or &" " or &"~"
Sorry for the inconvenience. Epinn "Epinn" wrote in message ... 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. Min text value: =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. Max text value: =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 "~"? Adding &"" to the end of the COUNTIF formula forces a count 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 "Ron Coderre" wrote in message ... ....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 "T. Valko" wrote: 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 "Carlo" wrote in message ... 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 |
All times are GMT +1. The time now is 10:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com