Hi,
I have a list of names and I need to get the name with the highest in the
alphabetical order. I dont want to sort.
Thanks,
Hi,
I have a list of names and I need to get the name with the highest in the
alphabetical order. I dont want to sort.
Thanks,
highest??
--
Don Guillett
SalesAid Software
donaldb@281.com
"Calaw" <Calaw@discussions.microsoft.com> wrote in message
news:22416431-8101-4194-A04E-7F9FF5FAE7AC@microsoft.com...
> Hi,
> I have a list of names and I need to get the name with the highest in the
> alphabetical order. I dont want to sort.
> Thanks,
On Thu, 10 Nov 2005 07:20:15 -0800, "Calaw" <Calaw@discussions.microsoft.com>
wrote:
>Hi,
>I have a list of names and I need to get the name with the highest in the
>alphabetical order. I dont want to sort.
>Thanks,
Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ (Version 4.01 or later).
If by "Highest" you mean that A is higher than B and so forth, use the formula:
=VSORT(Names,,1)
If you mean Z is higher than Y and so forth, then:
=VSORT(Names)
will do.
--ron
Here's another way...
If Z is higher than Y, try...
=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A10),1),COUNTIF(A1:A10,"<
"&A1:A10),0))
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
If A is higher than B, try...
=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10),1),COUNTIF(A1:A10,"<
"&A1:A10),0))
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
In article <22416431-8101-4194-A04E-7F9FF5FAE7AC@microsoft.com>,
"Calaw" <Calaw@discussions.microsoft.com> wrote:
> Hi,
> I have a list of names and I need to get the name with the highest in the
> alphabetical order. I dont want to sort.
> Thanks,
On Thu, 10 Nov 2005 11:49:07 -0500, Domenic <domenic22@sympatico.ca> wrote:
>Here's another way...
>
>If Z is higher than Y, try...
>
>=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A10),1),COUNTIF(A1:A10,"<
>"&A1:A10),0))
>
>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
>If A is higher than B, try...
>
>=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10),1),COUNTIF(A1:A10,"<
>"&A1:A10),0))
>
>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
>Hope this helps!
>
>In article <22416431-8101-4194-A04E-7F9FF5FAE7AC@microsoft.com>,
> "Calaw" <Calaw@discussions.microsoft.com> wrote:
>
>> Hi,
>> I have a list of names and I need to get the name with the highest in the
>> alphabetical order. I dont want to sort.
>> Thanks,
Nice
--ron
Domenic wrote...
>Here's another way...
>
>If Z is higher than Y, try...
>
>=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A10),1),
>COUNTIF(A1:A10,"<>"&A1:A10),0))
>
>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
>If A is higher than B, try...
>
>=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10),1),
>COUNTIF(A1:A10,"<>"&A1:A10),0))
>
>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
....
Since any instance of the 'highest' value would be as good as any
other, both formulas could be reduced to
=LOOKUP(2,1/(COUNTIF(A1:A30,">"&A1:A30)=0),A1:A30)
and
=LOOKUP(2,1/(COUNTIF(A1:A30,"<"&A1:A30)=0),A1:A30)
respectively, neither of which require array entry.
Nice Harlan! Actually you pointed it out to me on another occasion but
I completely forgot about it. Thanks for reminding me. It's definitely
much more preferable than the one I offered.
In article <1131652314.568739.327420@f14g2000cwb.googlegroups.com>,
"Harlan Grove" <hrlngrv@aol.com> wrote:
> Domenic wrote...
> >Here's another way...
> >
> >If Z is higher than Y, try...
> >
> >=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A10),1),
> >COUNTIF(A1:A10,"<>"&A1:A10),0))
> >
> >...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> >
> >If A is higher than B, try...
> >
> >=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10),1),
> >COUNTIF(A1:A10,"<>"&A1:A10),0))
> >
> >...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> ...
>
> Since any instance of the 'highest' value would be as good as any
> other, both formulas could be reduced to
>
> =LOOKUP(2,1/(COUNTIF(A1:A30,">"&A1:A30)=0),A1:A30)
>
> and
>
> =LOOKUP(2,1/(COUNTIF(A1:A30,"<"&A1:A30)=0),A1:A30)
>
> respectively, neither of which require array entry.
On 10 Nov 2005 11:51:54 -0800, "Harlan Grove" <hrlngrv@aol.com> wrote:
>Domenic wrote...
>>Here's another way...
>>
>>If Z is higher than Y, try...
>>
>>=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A10),1),
>>COUNTIF(A1:A10,"<>"&A1:A10),0))
>>
>>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>>
>>If A is higher than B, try...
>>
>>=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10),1),
>>COUNTIF(A1:A10,"<>"&A1:A10),0))
>>
>>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>...
>
>Since any instance of the 'highest' value would be as good as any
>other, both formulas could be reduced to
>
>=LOOKUP(2,1/(COUNTIF(A1:A30,">"&A1:A30)=0),A1:A30)
>
>and
>
>=LOOKUP(2,1/(COUNTIF(A1:A30,"<"&A1:A30)=0),A1:A30)
>
>respectively, neither of which require array entry.
Nicer.
--ron
Agreed! Definitely nicer!
In article <nau7n15ja0kju8gsnpprrk7fv0t9q3fp28@4ax.com>,
Ron Rosenfeld <ronrosenfeld@nospam.org> wrote:
> On 10 Nov 2005 11:51:54 -0800, "Harlan Grove" <hrlngrv@aol.com> wrote:
>
> >Domenic wrote...
> >>Here's another way...
> >>
> >>If Z is higher than Y, try...
> >>
> >>=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A10),1),
> >>COUNTIF(A1:A10,"<>"&A1:A10),0))
> >>
> >>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> >>
> >>If A is higher than B, try...
> >>
> >>=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10),1),
> >>COUNTIF(A1:A10,"<>"&A1:A10),0))
> >>
> >>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> >...
> >
> >Since any instance of the 'highest' value would be as good as any
> >other, both formulas could be reduced to
> >
> >=LOOKUP(2,1/(COUNTIF(A1:A30,">"&A1:A30)=0),A1:A30)
> >
> >and
> >
> >=LOOKUP(2,1/(COUNTIF(A1:A30,"<"&A1:A30)=0),A1:A30)
> >
> >respectively, neither of which require array entry.
>
> Nicer.
> --ron
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks