Top 25 Companies

[zinah]

Hi,

I have a monthly/quarterly report that shows top 25 companies that we gained employees from. What I need to build a dynamic list that can be refreshed whenever I dump the updated data file and formula can be refreshed to update my list. I know using some formulas like rank, filter and other great formulas can be helpful but not sure how to begin. Below is a sample table:

What I need to accomplish is: when I update the top 25 to any number i.e. 30 or 10, then what will happen is a list to be generated (just like when we use filter function) with a list that is showing the top 25 companies that we gained employees from (based on count of Job Req) that is sorted by largest number

Job Req ID	Previous company​	Department​
JR100​	AAA​	HR​				Top​	25​
JR200​	BBB​	Finance​				Department​	Company​	Count of Job Req​
JR300​	CCC​	Operations​				HR​	AAA​	3​
JR400​	DDD​	IT​				Finance​	AAA​	0​
JR500​	EEE​	HR​				Operations​	AAA​	0​
JR600​	FFF​	Finance​				IT​	AAA​	1​
JR700​	GGG​	IT​
JR800​	HHH​	Operations​
JR900​	AAA​	Legal​
JR1000​	BBB​	Customer Service​
JR1100​	CCC​	HR​
JR1200​	DDD​	Finance​
JR1300​	EEE​	Operations​
JR1400​	FFF​	IT​
JR1500​	GGG​	HR​
JR1600​	HHH​	Finance​
JR1700​	AAA​	IT​
JR1800​	BBB​	Operations​
JR1900​	CCC​	Legal​
JR2000​	DDD​	Customer Service​
JR2100​	AAA​	HR​
JR2200​	BBB​	Finance​
JR2300​	CCC​	Operations​
JR2400​	DDD​	IT​
JR2500​	EEE​	HR​
JR2600​	FFF​	Finance​
JR2700​	GGG​	IT​
JR2800​	HHH​	Operations​
JR2900​	AAA​	Legal​
JR3000​	BBB​	Customer Service​
JR3100​	CCC​	HR​
JR3200​	DDD​	Finance​
JR3300​	EEE​	Operations​
JR3400​	FFF​	IT​
JR3500​	GGG​	HR​
JR3600​	HHH​	Finance​
JR3700​	AAA​	HR​
JR3800​	BBB​	Finance​
JR3900​	CCC​	Operations​
JR4000​	DDD​	IT​

 

[zinah]

The 3 needs to be changed to the column index that needs to be sorted.

Thank you as usual for providing me with the formula, I made some tweaks and it worked perfectly great.

=UNIQUE(SORT(FILTER(FILTER(Sheet1!$BA:$CM,Sheet1!$CM:$CM>=LARGE(Sheet1!$CM:$CM,Sheet2!$G$2)),{1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1}),3,-1))

The BA:CM is my data table
CM: is the count column
{1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1}: I filtered out all the unwanted columns by 0 and kept the one I want by 1, I know it's a long one but it worked

Thank you and the rest of the of members who offered their solutions.

 

[Fluff]

Glad you sorted it & thanks for the feedback.