A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help using the LARGE function "WITHOUT" using an array



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 02:40 AM posted to microsoft.public.excel.worksheet.functions
exceluser
external usenet poster
 
Posts: 4
Default Help using the LARGE function "WITHOUT" using an array

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(Data !$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 ?
  #2  
Old June 3rd, 2010, 11:33 AM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Help using the LARGE function "WITHOUT" using an array

Hi, this does not have to be array-entered, if that's what you mean.

=IFERROR(INDEX(Data!$A$1:$A$5,MATCH(LARGE(INDEX(
(Data!$B$2:$B$5=$A2)*Data!$C$2:$C$5,),B$1),Data!$C $1:$C$5,0)),"")

BTW, this (and your original) would fail if you had two products of the same
weight and type.

HTH
Steve D.


"exceluser" wrote in message
...
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(Data !$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 ?


  #3  
Old June 3rd, 2010, 01:52 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Help using the LARGE function "WITHOUT" using an array

If you need to allow for two products of the same weight and type, try this:

=IFERROR(INDEX(Data!$A$1:$A$10,MATCH(LARGE(INDEX(
(Data!$B$2:$B$10=$A2)*Data!$C$2:$C$10+
ROW(Data!$C$2:$C$10),),B$1),INDEX(Data!$C$2:$C$10+
ROW(Data!$C$2:$C$10),),0)+1),"")



"Steve Dunn" wrote in message
...
Hi, this does not have to be array-entered, if that's what you mean.

=IFERROR(INDEX(Data!$A$1:$A$5,MATCH(LARGE(INDEX(
(Data!$B$2:$B$5=$A2)*Data!$C$2:$C$5,),B$1),Data!$C $1:$C$5,0)),"")

BTW, this (and your original) would fail if you had two products of the
same weight and type.

HTH
Steve D.


"exceluser" wrote in message
...
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(Data !$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 ?



  #4  
Old June 3rd, 2010, 02:54 PM posted to microsoft.public.excel.worksheet.functions
Bernd P
external usenet poster
 
Posts: 613
Default Help using the LARGE function "WITHOUT" using an array

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 at
http://sulprobil.com/html/sorting.html

Regards,
Bernd
  #5  
Old June 4th, 2010, 01:47 AM posted to microsoft.public.excel.worksheet.functions
exceluser
external usenet poster
 
Posts: 4
Default Help using the LARGE function "WITHOUT" using an array

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

  #6  
Old June 4th, 2010, 11:34 PM posted to microsoft.public.excel.worksheet.functions
exceluser
external usenet poster
 
Posts: 4
Default Help using the LARGE function "WITHOUT" using an array

On Jun 3, 8:52*am, "Steve Dunn" wrote:
If you need to allow for two products of the same weight and type, try this:

=IFERROR(INDEX(Data!$A$1:$A$10,MATCH(LARGE(INDEX(
(Data!$B$2:$B$10=$A2)*Data!$C$2:$C$10+
ROW(Data!$C$2:$C$10),),B$1),INDEX(Data!$C$2:$C$10+
ROW(Data!$C$2:$C$10),),0)+1),"")

"Steve Dunn" wrote in message

...



Hi, this does not have to be array-entered, if that's what you mean.


=IFERROR(INDEX(Data!$A$1:$A$5,MATCH(LARGE(INDEX(
(Data!$B$2:$B$5=$A2)*Data!$C$2:$C$5,),B$1),Data!$C $1:$C$5,0)),"")


BTW, this (and your original) would fail if you had two products of the
same weight and type.


HTH
Steve D.


"exceluser" wrote in message
...
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(Data !$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 ?- Hide quoted text -


- Show quoted text -


Steve,

You are so THE MAN !

It makes me almost forget that whole BP thing ... for a few hours
anyway.

The reason why I wanted to avoid using an array was because the
original formula I was using with an array (in over 15,000 cells) was
using %100 of the CPU for approximately

90 minutes on an Intel 3.4 GHz processor with 2 GB of RAM.

To make matters worse, new data is imported daily and making any
change effecting those cells would cause a 90 minute recalculation.

This new formula has reduced the calculation from 90 minutes to
under 30 seconds.

Could you explain why ... ?

1) The second INDEX function skips the first row for 'Data!$B$2:$B$5
and 'Data!$C$2:$C$5 rather than using the whole column

2) The second INDEX function multiplies those two ranges

Thanks again for that super fast formula.



Exceluser
  #7  
Old June 5th, 2010, 11:17 PM posted to microsoft.public.excel.worksheet.functions
exceluser
external usenet poster
 
Posts: 4
Default Help using the LARGE function "WITHOUT" using an array

On Jun 3, 8:52*am, "Steve Dunn" wrote:
If you need to allow for two products of the same weight and type, try this:

=IFERROR(INDEX(Data!$A$1:$A$10,MATCH(LARGE(INDEX(
(Data!$B$2:$B$10=$A2)*Data!$C$2:$C$10+
ROW(Data!$C$2:$C$10),),B$1),INDEX(Data!$C$2:$C$10+
ROW(Data!$C$2:$C$10),),0)+1),"")

"Steve Dunn" wrote in message

...



Hi, this does not have to be array-entered, if that's what you mean.


=IFERROR(INDEX(Data!$A$1:$A$5,MATCH(LARGE(INDEX(
(Data!$B$2:$B$5=$A2)*Data!$C$2:$C$5,),B$1),Data!$C $1:$C$5,0)),"")


BTW, this (and your original) would fail if you had two products of the
same weight and type.


HTH
Steve D.


"exceluser" wrote in message
...
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(Data !$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 ?- Hide quoted text -


- Show quoted text -


Steve,

Is there a way to use the second formula when sorting with a mix of
positive and negative numbers ?




Exceluser
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:41 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.