Re: Pass an array to Rank

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