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:

[TABLE="width: 387"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Client Code[/TD]
[TD]Salesperson[/TD]
[TD]Sales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]Jeff[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]Lisa[/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Tom[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Albert[/TD]
[TD]350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Karen[/TD]
[TD]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD]Karen[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD]Ana[/TD]
[TD]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Table where I want the names displayed:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client Code[/TD]
[TD]Salesperson #1[/TD]
[TD]Salesperson #2[/TD]
[TD]Salesperson #3[/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD] ?[/TD]
[TD] ?[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD] ?[/TD]
[TD] ?[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD] ?[/TD]
[TD] ?[/TD]
[TD] ?[/TD]
[/TR]
</tbody>[/TABLE]

 

[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)