On Jun 3, 9:54*am, Bernd P wrote:
On 3 Jun., 02:40, exceluser wrote:





Can someone show me how to accomplish the following by using the LARGE
function "WITHOUT" using an array ?

I'm trying to rank each product (most, second most, etc.) by weight.

* *Data worksheet

* * *A * * * * *B * * * * * * * C
* *1 Product * *Type * * * * * *Pounds
* *2 Orange * * Fruit * * * * * 600
* *3 Tomato * * Vegetable * * * * * * * 500
* *4 Apple * * * * * * *Fruit * * * * * 700
* *5 Potato * * * * * * Vegetable * * * * * * * 1,000

Using the LARGE function, the goal is to get the following result on
another worksheet:

* *Order worksheet

* * *A * * * * *B * * * * * * * C
* *1 Product * *1 * * * * * * * 2
* *2 Fruit * * * * * * *Apple * * * * * Orange
* *3 Vegetable *Potato * * * * *Tomato

On the Order worksheet, the following formula is the one I'm using
with an array:

* *{=IFERROR(INDEX(Data!$A$1:$C$5,MATCH(LARGE(IF(Dat a!$B$1:$B$5=
$A2,Data!$C$1:$C$5,""),B$1),Data!$C$1:$C$5,0),1)," ")}

Does anyone know how to get the same result without using an array -
even if it uses another function ?

Hello,

I suggest to use array functions to get your result right:
In sheet Data, enter into D1 "Type Rank" and into D2
=SUMPRODUCT(--($B2=$B$2:$B$999),--($C2<$C$2:$C$999))+SUMPRODUCT(--($B2=
$B$2:$B2),--($C2=$C$2:$C2))
and copy down.

Then you can array-enter in sheet Order into B2:
=INDEX(Data!$A$2:$A$999,MATCH($A2&"|"&B$1,Data!$B$ 2:$B$999&"|"&Data!$D
$2:$D$999,0))
and copy down and across.

If you like to compare with other approaches, test with Pounds data
like 1,1,1,1 or 0,0,0,0, for example.

Further examples you can find athttp://sulprobil.com/html/sorting.html

Regards,
Bernd- Hide quoted text -

- Show quoted text -

Bernd,

Thank you very much for that fast and very detailed response.

The reason that I'm trying to avoid using an array is that the
sheer number of cells that would contain this formula (15,000+) would
cause my computer to lockup for over an hour and a half while it
recalculates.

I recently replaced an array with a formula using the SUMIF
function and that rewrite alone reduced the calculation time from 2.5
hours to 1.5 hours.

Now I'm just trying to eliminate that last 1.5 hours which is the
reason for my original post.

It looks like I'll be able to avoid the array with the formula that
Steve suggested above.

I also checked out your website and it was very helpful.

Again, I appreciate the time you spent creating the solution above.




Exceluser