RANK Formula with Criteria

[hrayani]

Hello Friends,

I need a Rank formula based on criteria

Here is a sample data (having Names in A2:A8 & Qty in D2:D8)

NAMES Qty
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Peter[/TD]
[TD="width: 64, align: right"]25[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Required names (rank wise) based on sum of Quantites in Column D

In this case answer should be

John (coz john has the maximum qty of 147)
Peter (then peter with a qty of 126)
Mike (then Mike with a qty of 61)

Any help would be appreciated.

Regards,

Humayun

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

NAMES​

[/td][td][/td][td][/td][td]

Qty​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Peter​

[/td][td][/td][td][/td][td]

25​

[/td][td][/td][td]

John​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

John​

[/td][td][/td][td][/td][td]

99​

[/td][td][/td][td]

Peter​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Mike​

[/td][td][/td][td][/td][td]

36​

[/td][td][/td][td]

Mike​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Mike​

[/td][td][/td][td][/td][td]

25​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

John​

[/td][td][/td][td][/td][td]

48​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Peter​

[/td][td][/td][td][/td][td]

99​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Peter​

[/td][td][/td][td][/td][td]

2​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in F2 copied down
=IFERROR(INDEX(A$2:A$8,MATCH(LARGE(IF(ISNA(MATCH(A$2:A$8,F$1:F1,0)),SUMIF(A$2:A$8,A$2:A$8,D$2:D$8)),1),IF(ISNA(MATCH(A$2:A$8,F$1:F1,0)),SUMIF(A$2:A$8,A$2:A$8,D$2:D$8)),0)),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[hrayani]

Hi Marcelo,

Formula you provided is working Perfect.

Thanks a lot

Humayun

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[aqeelnokia99]

simply make pivort table sum qty and then sort by largest to smallest

 

[hrayani]

Hi aqeel,

Sorry i am not at all familiar with Pivot table

 

[hrayani]

Hi marcelo,

I Just noticed that the formula is taking too long to calculate...
The source data contains 1000 rows...

And also the source data file has to be open for the formula to give results.

Is there any solution to it ??

 

[kvsrinivasamurthy]

I have given two solutions.Tested for Data for row 3 to row 1682.
1. With helper column C, very very fast.

In C3 then copied down till data in A column.

=IF((ROW()-ROW($A$2))=MATCH(A3,$A$3:$A3,0),SUMIF($A$3:$A$1682,$A3,$B$3:$B$1682),"")

For name list in E2 then copied down till blank cell is seen.

=IFERROR(INDEX($A$3:$A$1682,MATCH(LARGE($C$3:$C$1682,ROWS($E$2:$E2)),$C$3:$C$1682,0)),"")

2. Without helper column, takes more time.Approx 1 sec for each cell.

In G3 then copied down till blank cell is seen.

=IFERROR(INDEX($A$3:$A$1682,SMALL(IF(SUMIF($A$3:$A$1682,$A$3:$A$1682,$B$3:$B$1682)*(COUNTIF($G$2:$G2,$A$3:$A$1682)=0)=LARGE(IF(COUNTIF($G$2:$G2,$A$3:$A$1682)=0,SUMIF($A$3:$A$1682,$A$3:$A$1682,$B$3:$B$1682),""),1),ROW($A$3:$A$1682),""),1)-ROW($G$3)+1),"")

 

[hrayani]

Hi kvsrinivasamurthy ,

I tried the second solution one without the helper column...
It is working but its repeating some text when the unique text are over...

For example
If the date in rows 3 to 1682 contains text but with not more then 11 unique text
then the formula result is showing all the unique values till row 11 (that's fine) but in row 12 its repeating some text from the source date i.e. rows 3 to 1682..


EDIT POST..

The first solution works fine

 

[kvsrinivasamurthy]

Both the formulas are working ok for me. If possible upload file