How to list top 15 broker is a list based on the frequency of how many times it appears

[Tennisguuy]

I am trying to list the top 15 brokers from a list of accounts 300 accounts.

Name of Prospect	Eff. Date	Description	Broker	Broker Type	Status	Premium		# of Sub
ACM Management	9/27/2019	Remidation Contractor	James Willis	Retail	Declined-Und	100,000		4
ACME Serives	9/12/2019	Remidation Contractor	James Willis	Wholesale	Declined-Und	25,000		4
Walter Baker	10/7/2019	Mold remidation Contractor	All Insurance	Wholesale	Declined-Broker	35,235		2
Jackson Environmental	10/28/2019	Environmental Contractors	James White Insurance		Not Sold	12,666		1
Lacy's Express	11/6/2019	Hazardous Waste Hauler	All Insurance	Wholesale	Declined-Und	36,584		2
Kropp Environmental	11/22/2019	Environmental Contractors	All Risk	Wholesale	Not Sold	75,000		1
Penn Turf Inc.	11/10/2019	Recycling Center	Jimcor Agency	Wholesale	Declined-Und	63,212		1
AllliedBean Demolition Inc	11/23/2019	Demolition Contractor	James Willis	Wholesale	All Other	10,235		4
Horwith Trucks	12/1/2019	Hazardous Waste Hauler	James Willis	Wholesale	Declined-Und	15,000		4
ETI Environmental	12/1/2019	Tunneling and Bridge Cleaning	Brown Surplus		Declined-Und	25,000		1
ERC ACQ Inc	12/31/2019	Environmental Contractors	ACME Insurance		Not Sold	35,000		1
Power Components Systems	12/24/2019	Environmental Contractors	Howard W. Phillips		Not Sold	60,000		1
John Excavtion	12/11/2019	Environmental Recycler	White and Baker	Wholesale	All Other			1
Red Dirt Septic and Backhoe	12/3/2019	Septic tank install and clean	ABC Agency	Wholesale	Declined-Und			3
SJ Management-South Jersey Group	12/2/2019	Environmental Contractors	ABC Agency		Declined-Und			3
Preferred Drilling Solutions	12/31/2019	Remidation and drilling contractor	ABC Agency	Retail	Declined-Und			3

In column O I have the name of the prospect. In cell W2 I have the following formula IF(O2="","",COUNTIF($R$2:$R$300,R2)) and I copied all the way too down row 300. I used this formula to count the number of time a particular broker has appear.

Broker	# of Subs	Broker Type	Status	Premium
James Willis	4
James Willis	4
James Willis	4
ABC Agency	3
ABC Agency	3
ABC Agency	3
All Insurance	2
All Insurance	2
James White Insurance	1

In B2 I have the following formula IF(C2="","",INDEX($R$2:$R$500,MATCH(C2,$W$2:$W$500,0))). I want to list the broker in column B that show the broker only once based on the frequency in which is appears from column from column W. The formula I have in B2 is kind of working but it is showing the brokers the number of times it appears based on the highest frequency and I only want it to list it once based on highest frequency. I want to list the top 15 brokers based on the highest frequency but only list it once. The other issue is when a frequency number is the same showing different broker with the same frequency. I aslo tried using a Rank to do it but got the same results. I was going to use vlookup formula in cell D2:F2 to pull that data.

 

[ExcelGzh]

You are going to need 2 helper columns to do this. In the column to the right of # of Sub column (Column X) put this formula:

=IF(COUNTIFS(R$1:R2,R2)=1,W2,"")

This will eliminate the duplicate counts for each broker.

Then in the next column after that (Column Y), put this formula:

=IF(LEN(X2)>0,RANK(X2,X$2:X$17)+COUNTIFS(X$2:X2,X2)-1,"")

This will rank the values produced by the previous formula and add 1 for each different broker on the same score. The result is a numerically sequential ranking list.

Then with the rankings listed from 1 to whatever in column A, put this formula in column B:

=IFERROR(INDEX(R:R,MATCH(A2,Y:Y,0),1),"")

The IFERROR is only needed if the number of ranks in column A exceeds the number of ranks in column Y

 

[Fluff]

Please update your account details to show what version of Xl you are using & what platform. It enables members to offer something that will work for you.

If you have dynamic arrays you could use

+Fluff.xlsm
	B	C	D	E	F								N	O	P	Q	R	S	T	U	V	W
1	Broker	# of Subs	Broker Type	Status	Premium									Name of Prospect	Eff. Date	Description	Broker	Broker Type	Status	Premium		# of Sub
2	James Willis	4												ACM Management	9/27/2019	Remidation Contractor	James Willis	Retail	Declined-Und	100,000		4
3	ABC Agency	3												ACME Serives	09/12/2019	Remidation Contractor	James Willis	Wholesale	Declined-Und	25,000		4
4	All Insurance	2												Walter Baker	10/07/2019	Mold remidation Contractor	All Insurance	Wholesale	Declined-Broker	35,235		2
5	James White Insurance	1												Jackson Environmental	10/28/2019	Environmental Contractors	James White Insurance		Not Sold	12,666		1
6	All Risk	1												Lacy's Express	11/06/2019	Hazardous Waste Hauler	All Insurance	Wholesale	Declined-Und	36,584		2
7	Jimcor Agency	1												Kropp Environmental	11/22/2019	Environmental Contractors	All Risk	Wholesale	Not Sold	75,000		1
8	Brown Surplus	1												Penn Turf Inc.	11/10/2019	Recycling Center	Jimcor Agency	Wholesale	Declined-Und	63,212		1
9	ACME Insurance	1												AllliedBean Demolition Inc	11/23/2019	Demolition Contractor	James Willis	Wholesale	All Other	10,235		4
10	Howard W. Phillips	1												Horwith Trucks	12/01/2019	Hazardous Waste Hauler	James Willis	Wholesale	Declined-Und	15,000		4
11	White and Baker	1												ETI Environmental	12/01/2019	Tunneling and Bridge Cleaning	Brown Surplus		Declined-Und	25,000		1
12														ERC ACQ Inc	12/31/2019	Environmental Contractors	ACME Insurance		Not Sold	35,000		1
13														Power Components Systems	12/24/2019	Environmental Contractors	Howard W. Phillips		Not Sold	60,000		1
14														John Excavtion	12/11/2019	Environmental Recycler	White and Baker	Wholesale	All Other			1
15														Red Dirt Septic and Backhoe	12/03/2019	Septic tank install and clean	ABC Agency	Wholesale	Declined-Und			3
16														SJ Management-South Jersey Group	12/02/2019	Environmental Contractors	ABC Agency		Declined-Und			3
17														Preferred Drilling Solutions	12/31/2019	Remidation and drilling contractor	ABC Agency	Retail	Declined-Und			3
18
19
Data

Cell Formulas
Range	Formula
B2:B11	B2	=UNIQUE(FILTER(SORTBY(R2:R100,W2:W100,-1),R2:R100<>""))
C2:C11	C2	=XLOOKUP(B2#,R2:R100,W2:W100)
Dynamic array formulas.

 

[Tennisguuy]

Guys thanks for your responses. I really really appreciate it. ExcelGZH your method worked. Fluff I updated my details. I have a subscription to 365 so I always have the most updated version. However at work they only have excel 2016 so that is what I have to use this on. I tried your method on the 2016 version and I got an error message because of the version is not current. Therefore, I tried it on my office 365 and I didn't get the error message but the results were blank.

 

[Fluff]

Thanks for updating your profile
Not sure why my solution didn't work for you, but glad you have something that works & thanks for the feedback

 

[Tennisguuy]

Fluff I am going to double check to make sure I did everything correctly and again thanks for your help.

 

[Fluff]

My pleasure

 

[Fluff]

Another option for B2 with the dynamic arrays, would be
=UNIQUE(FILTER(SORT(FILTER(R2:W200,R2:R200<>""),6,-1),COLUMN(R2:W2)=18))