Rank by Person, without Repeats across people

[esseispercipi4]

I have a set of data that is salespeople (column A), and how much they sold (column C) by client (Column B). I need to get the top 5 clients by sales for each salesperson; however, no duplicates across salespeople.
So if John Wilkes had $10M to Fun Company as his #1 , and Jane Bridges had $8M to Fun Company as her #1 , John Wilkes would have Fun Company as his #1 , and Jane Bridges would have Fun Company skipped, and her #2 would become her new #1 .

Any idea at all how to do this? I've tried all kinds of helper columns and formulas and still can't work it out.

 

[VBA Geek]

Hello

I am not sure that I have understood it 100%, but probably the below will help you:

In column D you will find the rank by sales person as it would normally happen without looking at your specific requirements.

Then column G would be the ranking matching your requirements, using column E and F as helper columns (could most likely be done in a single formula but a set up like this helps us understand)

Table and formulas below:

Excel 2013/2016
	A	B	C	D	E	F	G
1	SalesPerson	Client	Amount	Rank By Sales Person (RAW)	Rank By Client	New Amount To Rank By	Rank By Sales Person (as requested)
2	A	2	377	3	2
3	A	3	353	5	2
4	A	9	396	2	1	396	2
5	A	10	360	4	1	360	3
6	A	11	162	6	3
7	A	12	155	7	1	155	4
8	A	13	439	1	1	439	1
9	B	1	331	3	1	331	3
10	B	2	414	2	1	414	2
11	B	5	171	4	1	171	4
12	B	7	512	1	1	512	1
13	B	14	158	5	1	158	5
14	C	2	298	1	3
15	C	9	108	3	2
16	C	11	224	2	2
17	D	1	294	5	2
18	D	3	430	2	1	430	2
19	D	6	632	1	1	632	1
20	D	8	256	6	1	256	5
21	D	11	323	4	1	323	4
22	D	15	330	3	1	330	3
Sheet2

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$C$2:$C$22,">"&C2,$A$2:$A$22,A2</font>)+1</td></tr></tbody></table></td></tr></table><br />

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$C$2:$C$22,">"&C2,$B$2:$B$22,B2</font>)+1</td></tr></tbody></table></td></tr></table><br />

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=IF(<font color="Blue">E2=1,C2,""</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=IF(<font color="Blue">F2="","",COUNTIFS(<font color="Red">$F$2:$F$22,">"&F2,$A$2:$A$22,A2,$F$2:$F$22,"<>"</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[esseispercipi4]

This is similar to approaches I've attempted and it gets most of the way there.
However, the issue this does not address is:
If Salesperson B sold 2 units to client 16 and Salesperson E sold 1 unit to client 16 as their only sale in the year.
Client 16 would be ranked #6 for Salesperson B, which means it would be excluded from their list. That means it should show up on Salesperson E's list as they are the next highest seller. These formulas skip over them still, meaning that Client 16 is missing entirely from the list of "Top Clients by Salesperson".