Re: Pass an array to Rank

Hi RD!

That works. I'll put this one in my "stash", also.

Thanks

Biff

"RagDyer" <RagDyer@xxxxxxxxxxxxx> wrote in message
news:upkiTiFfFHA.4092@xxxxxxxxxxxxxxxxxxxxxxx
> Would this "auto sort" *array* formula of Harlan's be of any help?
>
> =INDEX($D$1:$D$10,MATCH(SMALL(COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),ROW()-ROW($
> E$1)+1),COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),0))
>
> The "E1" is not a typo!
> It's the first cell that you enter the formula in.
>
> This works for *all* text, OR *all* numbers.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
>
> "Biff" <biffinpitt@xxxxxxxxxxx> wrote in message
> news:u$FVBtEfFHA.3584@xxxxxxxxxxxxxxxxxxxxxxx
>> That could be done but it would have to done every time the data changes.
> A
>> macro could also be used but the macro would have to run every time the
> data
>> changes. I'm looking for a completely automated operation and the formula
>> route satisfies that requirement. It's just a matter of trying to
> eliminate
>> the need for the helper columns. I can live with what I have!
>>
>> Biff
>>
>> "Alan Beban" <unavailable@xxxxxx> wrote in message
>> news:%23UTvWhEfFHA.3448@xxxxxxxxxxxxxxxxxxxxxxx
>> > If you simply copy the list to another range and then perform
>> > Data/Sort,
>> > Ascending, it will return
>> >
>> > black
>> > blue
>> > grass
>> > green
>> > green
>> >
>> > Is that satisfactory?
>> >
>> > Alan Beban
>> >
>> > Biff wrote:
>> >> 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
>> >>
>> >>
>>
>


.

Loading