Tennisguuy
Well-known Member
- Joined
- Oct 17, 2007
- Messages
- 564
- Office Version
- 2016
- Platform
- Windows
I am trying to list the top 15 brokers from a list of accounts 300 accounts.
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.
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.
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.