Ranking with Ties

ellyzadg

ellyzadg

Board Regular
Joined
May 9, 2016
Messages
75
Hi Everyone,

Would like to ask correct formula on how to deal with ranking tie ups. Thank you in advance!

Below are my sample data and desired result

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee ID[/TD]
[TD]Sales Person[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD]S00001[/TD]
[/TR]
[TR]
[TD]S00002[/TD]
[/TR]
[TR]
[TD]S00003[/TD]
[/TR]
[TR]
[TD]S00004[/TD]
[/TR]
[TR]
[TD]S00005[/TD]
[/TR]
[TR]
[TD]S00006[/TD]
[/TR]
[TR]
[TD]S00007[/TD]
[/TR]
[TR]
[TD]S00008[/TD]
[/TR]
[TR]
[TD]S00009[/TD]
[/TR]
[TR]
[TD]S00010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD]Richard Tan[/TD]
[/TR]
[TR]
[TD]Bernadeth Milante[/TD]
[/TR]
[TR]
[TD]Isamael Gamba[/TD]
[/TR]
[TR]
[TD]Grace Dineros[/TD]
[/TR]
[TR]
[TD]Jenette Calderon[/TD]
[/TR]
[TR]
[TD]Joseph Solidum[/TD]
[/TR]
[TR]
[TD]Leny Patricio[/TD]
[/TR]
[TR]
[TD]Marvin Delacroix[/TD]
[/TR]
[TR]
[TD]Jace Balaren[/TD]
[/TR]
[TR]
[TD]Liliana Vess[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD] $ 45,441.00[/TD]
[/TR]
[TR]
[TD] $ 12,543.00[/TD]
[/TR]
[TR]
[TD] $ 45,441.00[/TD]
[/TR]
[TR]
[TD] $ 23,144.00[/TD]
[/TR]
[TR]
[TD] $ 12,354.00[/TD]
[/TR]
[TR]
[TD] $ 35,311.00[/TD]
[/TR]
[TR]
[TD] $ 42,645.00[/TD]
[/TR]
[TR]
[TD] $ 42,623.00[/TD]
[/TR]
[TR]
[TD] $ 19,820.00[/TD]
[/TR]
[TR]
[TD] $ 16,336.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Outcome

[TABLE="width: 500"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Sales[/TD]
[TD]Salesperson (what i get)[/TD]
[TD]Desired Result[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD] $ 45,441.00[/TD]
[/TR]
[TR]
[TD] $ 45,441.00[/TD]
[/TR]
[TR]
[TD] $ 42,645.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD]Richard Tan[/TD]
[/TR]
[TR]
[TD]Richard Tan[/TD]
[/TR]
[TR]
[TD]Leny Patricio[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD]Richard Tan[/TD]
[/TR]
[TR]
[TD]Ismael Gamba[/TD]
[/TR]
[TR]
[TD]Leny Patricio[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Hi Everyone,

Would like to ask correct formula on how to deal with ranking tie ups. Thank you in advance!

Below are my sample data and desired result

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee ID[/TD]
[TD]Sales Person [/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD]S00001[/TD]
[/TR]
[TR]
[TD]S00002[/TD]
[/TR]
[TR]
[TD]S00003[/TD]
[/TR]
[TR]
[TD]S00004[/TD]
[/TR]
[TR]
[TD]S00005[/TD]
[/TR]
[TR]
[TD]S00006[/TD]
[/TR]
[TR]
[TD]S00007[/TD]
[/TR]
[TR]
[TD]S00008[/TD]
[/TR]
[TR]
[TD]S00009[/TD]
[/TR]
[TR]
[TD]S00010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD]Richard Tan[/TD]
[/TR]
[TR]
[TD]Bernadeth Milante[/TD]
[/TR]
[TR]
[TD]Isamael Gamba[/TD]
[/TR]
[TR]
[TD]Grace Dineros[/TD]
[/TR]
[TR]
[TD]Jenette Calderon[/TD]
[/TR]
[TR]
[TD]Joseph Solidum[/TD]
[/TR]
[TR]
[TD]Leny Patricio[/TD]
[/TR]
[TR]
[TD]Marvin Delacroix[/TD]
[/TR]
[TR]
[TD]Jace Balaren[/TD]
[/TR]
[TR]
[TD]Liliana Vess[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD] $ 45,441.00[/TD]
[/TR]
[TR]
[TD] $ 12,543.00 [/TD]
[/TR]
[TR]
[TD] $ 45,441.00[/TD]
[/TR]
[TR]
[TD] $ 23,144.00[/TD]
[/TR]
[TR]
[TD] $ 12,354.00[/TD]
[/TR]
[TR]
[TD] $ 35,311.00[/TD]
[/TR]
[TR]
[TD] $ 42,645.00[/TD]
[/TR]
[TR]
[TD] $ 42,623.00[/TD]
[/TR]
[TR]
[TD] $ 19,820.00[/TD]
[/TR]
[TR]
[TD] $ 16,336.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Outcome

[TABLE="width: 500"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Sales[/TD]
[TD]Salesperson (what i get)[/TD]
[TD]Desired Result [/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD] $ 45,441.00[/TD]
[/TR]
[TR]
[TD] $ 45,441.00[/TD]
[/TR]
[TR]
[TD] $ 42,645.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD]Richard Tan[/TD]
[/TR]
[TR]
[TD]Richard Tan[/TD]
[/TR]
[TR]
[TD]Leny Patricio[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD]Richard Tan[/TD]
[/TR]
[TR]
[TD]Ismael Gamba[/TD]
[/TR]
[TR]
[TD]Leny Patricio[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

*im using index match in salesperson column

Thanks!
 
Last edited:
Upvote 0
Hi, why not just sort the data largest to smallest by the sales amount? Maybe I'm missing something as well, but why would Richard Tan be rank 1 and 2? Wouldn't Isamael Gamba be either 1st or 2nd as well?
 
Upvote 0
KennyGreens said:
Hi, why not just sort the data largest to smallest by the sales amount? Maybe I'm missing something as well, but why would Richard Tan be rank 1 and 2? Wouldn't Isamael Gamba be either 1st or 2nd as well?
Click to expand...

Hi kenny, Richard and Ismael may interchange. Yes I can sort this , however our requirement is to provide a formula using index match and then consider when there are tie ups.
 
Upvote 0
Hi, here is an option you can try..


Excel 2013/2016
ABC
1Employee IDSales PersonSales
2S00001Richard Tan45441
3S00002Bernadeth Milante12543
4S00003Isamael Gamba45441
5S00004Grace Dineros23144
6S00005Jenette Calderon12354
7S00006Joseph Solidum35311
8S00007Leny Patricio42645
9S00008Marvin Delacroix42623
10S00009Jace Balaren19820
11S00010Liliana Vess16336
12
13
14
15Outcome
16
17RankSalesDesired Result
18145441Richard Tan
19245441Isamael Gamba
20342645Leny Patricio
Sheet1
Cell Formulas
RangeFormula
B18=LARGE($C$2:$C$11,A18)
C18=INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($C$2:$C$11=B18),COUNTIFS(B$18:B18,B18)))
 
Upvote 0
Heres a way. In B16:

=LARGE($C$2:$C$11,A16)

In C16 entered CTRL-SHIFT-ENTER:

=INDEX($B$2:$B$11,SMALL(IF($C$2:$C$11=B16,ROW($C$2:$C$11)-ROW($C$2)+1),COUNTIF($B$16:B16,B16)))
 
Upvote 0
I have merged your two threads together. In the future, please do not post the same question multiple times. Either edit the original post (I believe you have 10 minutes to do so), or reply to the original thread with your clarification.

Per forum rules, threads of a duplicate nature will typically be locked or deleted.
 
Upvote 0
Joe4 said:
I have merged your two threads together. In the future, please do not post the same question multiple times. Either edit the original post (I believe you have 10 minutes to do so), or reply to the original thread with your clarification.

Per forum rules, threads of a duplicate nature will typically be locked or deleted.
Click to expand...


Noted and thanks. My apologies I did not noticed I doubled the thread I edited. Thank you.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top