Display top salespeople per client

[TommyTenenbaum]

Dear all,
I have a file with some raw data where I have 3 columns: Client code / Salesperson / Sales (in $)
I would like to find a way to display the top 3 salesperson for each Client code in another table, with 4 Columns: Client Code / Salesperson 1 / Salesperson 2 / Salesperson 3.
I tried looking for ways to do that online, I found a sumproduct function with some double negatives that helped me to rank the salespeople but I didn't know what to do next with the ranks I got...

Please bear in mind that I don't have 3 clients but 10K+ and 7K+ Salespeople in my raw database.
You can find below a simple example to illustrate what I would like to get.
Thanks a lot for your help!!

Raw data:

Client Code	Salesperson	Sales
Client 1	Jeff	200
Client 1	Lisa	600
Client 2	Tom	100
Client 2	Albert	350
Client 2	Karen	400
Client 3	Karen	200
Client 3	Ana	400
Table where I want the names displayed:
Client Code	Salesperson #1	Salesperson #2	Salesperson #3
Client 1	?	?	?
Client 2	?	?	?
Client 3	?	?	?

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[lrobbo314]

I think that your best bet would be to turn your raw data into a Pivot Table. Put Client Code and Salesperson in the 'Rows' section and Sales in the 'Values' section.

 

[sheetspread]

I prefer the pivot table also here. If you must see the names instead, sort by client, then sales descending, and:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Client Code	Salesperson	Sales			Client Code	Salesperson #1	Salesperson #2	Salesperson #3
2	Client 1	Lisa	600			Client 1	Lisa	Jeff	#NUM!
3	Client 1	Jeff	200			Client 2	Karen	Albert	Tom
4	Client 2	Karen	400			Client 3	Ana	Karen	#NUM!
5	Client 2	Albert	350
6	Client 2	Tom	100
7	Client 3	Ana	400
8	Client 3	Karen	200
Sheet3

Cell Formulas
Range	Formula
G2	{=INDEX($B$1:$B$8,SMALL(IF($A$2:$A$8=$F2,ROW($A$2:$A$8)),COLUMN(A1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

(you can delete the #NUM!s with F5(Goto)-Special-Errors)