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

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
559
Office Version
  1. 2016
Platform
  1. Windows
I am trying to list the top 15 brokers from a list of accounts 300 accounts.

Name of ProspectEff. DateDescription BrokerBroker TypeStatusPremium# of Sub
ACM Management9/27/2019Remidation ContractorJames WillisRetailDeclined-Und100,0004
ACME Serives9/12/2019Remidation ContractorJames WillisWholesaleDeclined-Und25,0004
Walter Baker10/7/2019Mold remidation ContractorAll InsuranceWholesaleDeclined-Broker35,2352
Jackson Environmental10/28/2019Environmental ContractorsJames White InsuranceNot Sold12,6661
Lacy's Express11/6/2019Hazardous Waste HaulerAll InsuranceWholesaleDeclined-Und36,5842
Kropp Environmental11/22/2019Environmental ContractorsAll RiskWholesaleNot Sold75,0001
Penn Turf Inc.11/10/2019Recycling CenterJimcor AgencyWholesaleDeclined-Und63,2121
AllliedBean Demolition Inc11/23/2019Demolition ContractorJames WillisWholesaleAll Other10,2354
Horwith Trucks12/1/2019Hazardous Waste HaulerJames WillisWholesaleDeclined-Und15,0004
ETI Environmental12/1/2019Tunneling and Bridge CleaningBrown SurplusDeclined-Und25,0001
ERC ACQ Inc12/31/2019Environmental ContractorsACME InsuranceNot Sold35,0001
Power Components Systems12/24/2019Environmental ContractorsHoward W. PhillipsNot Sold60,0001
John Excavtion12/11/2019Environmental RecyclerWhite and BakerWholesaleAll Other1
Red Dirt Septic and Backhoe12/3/2019Septic tank install and cleanABC AgencyWholesaleDeclined-Und3
SJ Management-South Jersey Group12/2/2019Environmental ContractorsABC AgencyDeclined-Und3
Preferred Drilling Solutions12/31/2019Remidation and drilling contractorABC AgencyRetailDeclined-Und3

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 SubsBroker TypeStatusPremium
James Willis4
James Willis4
James Willis4
ABC Agency3
ABC Agency3
ABC Agency3
All Insurance2
All Insurance2
James White Insurance1

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.


 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFNOPQRSTUVW
1Broker# of SubsBroker TypeStatusPremiumName of ProspectEff. DateDescriptionBrokerBroker TypeStatusPremium# of Sub
2James Willis4ACM Management9/27/2019Remidation ContractorJames WillisRetailDeclined-Und100,0004
3ABC Agency3ACME Serives09/12/2019Remidation ContractorJames WillisWholesaleDeclined-Und25,0004
4All Insurance2Walter Baker10/07/2019Mold remidation ContractorAll InsuranceWholesaleDeclined-Broker35,2352
5James White Insurance1Jackson Environmental10/28/2019Environmental ContractorsJames White InsuranceNot Sold12,6661
6All Risk1Lacy's Express11/06/2019Hazardous Waste HaulerAll InsuranceWholesaleDeclined-Und36,5842
7Jimcor Agency1Kropp Environmental11/22/2019Environmental ContractorsAll RiskWholesaleNot Sold75,0001
8Brown Surplus1Penn Turf Inc.11/10/2019Recycling CenterJimcor AgencyWholesaleDeclined-Und63,2121
9ACME Insurance1AllliedBean Demolition Inc11/23/2019Demolition ContractorJames WillisWholesaleAll Other10,2354
10Howard W. Phillips1Horwith Trucks12/01/2019Hazardous Waste HaulerJames WillisWholesaleDeclined-Und15,0004
11White and Baker1ETI Environmental12/01/2019Tunneling and Bridge CleaningBrown SurplusDeclined-Und25,0001
12ERC ACQ Inc12/31/2019Environmental ContractorsACME InsuranceNot Sold35,0001
13Power Components Systems12/24/2019Environmental ContractorsHoward W. PhillipsNot Sold60,0001
14John Excavtion12/11/2019Environmental RecyclerWhite and BakerWholesaleAll Other1
15Red Dirt Septic and Backhoe12/03/2019Septic tank install and cleanABC AgencyWholesaleDeclined-Und3
16SJ Management-South Jersey Group12/02/2019Environmental ContractorsABC AgencyDeclined-Und3
17Preferred Drilling Solutions12/31/2019Remidation and drilling contractorABC AgencyRetailDeclined-Und3
18
19
Data
Cell Formulas
RangeFormula
B2:B11B2=UNIQUE(FILTER(SORTBY(R2:R100,W2:W100,-1),R2:R100<>""))
C2:C11C2=XLOOKUP(B2#,R2:R100,W2:W100)
Dynamic array formulas.
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
559
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 17, 2007
Messages
559
Office Version
  1. 2016
Platform
  1. Windows
Fluff I am going to double check to make sure I did everything correctly and again thanks for your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
My pleasure
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Another option for B2 with the dynamic arrays, would be
=UNIQUE(FILTER(SORT(FILTER(R2:W200,R2:R200<>""),6,-1),COLUMN(R2:W2)=18))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,638
Messages
5,626,038
Members
416,155
Latest member
bokboxes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top