Multi-Conditional Ranking with-out Duplicates

[stjacked]

Hello,

Trying to build a ranking formula that takes into account total score for duplicate identifiers, as well as a tie-break for the duplicate total scores. The goal is to return a rank for each, without skipping any ranks, with duplicates scores rankings based on the Tie-break. However, the identifiers would have the same rank as their duplicates.

The idea is there are 6 Sales people (Two of which have the same name, which can't be changed i.e. Dan A. & Dan B.). They need to be ranked based their aggregate sales in a set of data. A new line in the data set is added after every sale, so the same person can be in the list twice in the same list. Each sale has an associated number of Clients and the ranking is tie-broken by this number. Therefore, the Person with the highest sales (35) and the highest number of clients (5) would be ranked first, a person with the same sales (35) and 2 less clients (3) would be ranked 2nd and a person with lower sales (30), but higher clients (6) would be ranked 3rd. If the Person with the highest rank shows up on the list twice, their rank is that same as any other line.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Same Name?[/TD]
[TD]Sales[/TD]
[TD]Clients[/TD]
[TD]Agg. Sales[/TD]
[TD]Agg. Clients[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Yes[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Yes[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]30[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Sean[/TD]
[TD]Yes[/TD]
[TD]20[/TD]
[TD]4[/TD]
[TD]35[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]Yes[/TD]
[TD]30[/TD]
[TD]2[/TD]
[TD]35[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Yes[/TD]
[TD]25[/TD]
[TD]5[/TD]
[TD]30[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Sean[/TD]
[TD]Yes[/TD]
[TD]15[/TD]
[TD]1[/TD]
[TD]35[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Sean[/TD]
[TD]No[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]Yes[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]35[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]No[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Yes[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


Happy to clarify if something doesn't make sense. Thanks in advance for any advice.