Re: Pass an array to Rank

Hi Domenic!

I'm try to extract TEXT values sorted in ascending order. I can do it easily
but it takes 2 helper columns. So what I'm trying to do is put it all
together in a single formula and eliminate the need for the helpers. The
list has dupes and that's what's proving to be a real bear.

green
black
grass
blue
green

I want to extract sorted based on the first letter. It doesn't matter if
"black" or "blue" is listed first. So the extracted list would look like
this:

black
blue
green
green
grass

Right now I use 2 helpers, 1 returns the code for the first letter:

=CODE(UPPER(A1)

The other helper is the Rank that break ties:

=RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1

Then a simple INDEX/MATCH.

Trying to put that all together in one formula!

Good challenge for someone!

Biff

"Domenic" <domenic22@xxxxxxxxxxxx> wrote in message
news:domenic22-B8C33E.20312228062005@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Biff!
>
> Unfortunately, I have no idea. Does the solution have to involve the
> RANK function or are you willing to use another alternative to get
> ranking?
>
> In article <Oj312pDfFHA.1412@xxxxxxxxxxxxxxxxxxxx>,
> "Biff" <biffinpitt@xxxxxxxxxxx> wrote:
>
>> Hi Folks!
>>
>> Anyone know how to pass an array of values as the ref argument of the
>> Rank
>> function without hardcoding or using a range reference?
>>
>> Assume I have this array of values generated by another formula:
>> {71;66;83;71;84}
>>
>> How do I pass that array to Rank?
>>
>> I can get the array passed but then the formula #VALUE! errors:
>>
>> =RANK(71,{71;66;83;71;84})
>>
>> I've tried using a name for the array, Indexing, Indirect ???
>>
>> I've never seen this done but I'm not certain that it can't be done.
>>
>> Biff


.

Relevant Pages

  • Re: Pass an array to Rank
    ... > but it takes 2 helper columns. ... >> RANK function or are you willing to use another alternative to get ... >>> How do I pass that array to Rank? ...
    (microsoft.public.excel.worksheet.functions)
  • Extract unique values
    ... single formula, to extract values from col A that do not ... I can generate this array with the helper formula - ... But I can not figure a way to pass that array in a formula ...
    (microsoft.public.excel.worksheet.functions)
  • Re: After Trigger Updates
    ... You can use a helper table as Kass (tks) has demonstrated. ... want to take a look at this article for some insight as the reason behind ... You would use substringto extract each byte to compare. ...
    (microsoft.public.sqlserver.programming)
  • Re: Extract unique values
    ... put the following array formula in C1: ... "Jill" schrieb im Newsbeitrag ... > Sam Jim ... > I can generate this array with the helper formula - ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Find value in matrix
    ... helper wrote: ... <SNIP> ... I don't understand the memory difference you are referring ... array the same size as the original array. ...
    (comp.soft-sys.matlab)
Loading