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​

 

[maabadi]

Try This:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Job Req ID	Previous company	Department	Countifs	Rank
2	JR100	AAA	HR	1	21			Top	25
3	JR200	BBB	Finance	1	22		No.	Department	Company	Count of Job Req
4	JR300	CCC	Operations	1	23		1	HR	AAA	3
5	JR400	DDD	IT	1	24		2	Finance	BBB	3
6	JR500	EEE	HR	1	25		3	Operations	CCC	3
7	JR600	FFF	Finance	1	26		4	IT	DDD	3
8	JR700	GGG	IT	1	27		5	HR	AAA	2
9	JR800	HHH	Operations	1	28		6	Finance	BBB	2
10	JR900	AAA	Legal	1	29		7	Operations	CCC	2
11	JR1000	BBB	Customer Service	1	30		8	IT	DDD	2
12	JR1100	CCC	HR	1	31		9	HR	EEE	2
13	JR1200	DDD	Finance	1	32		10	Finance	FFF	2
14	JR1300	EEE	Operations	1	33		11	IT	GGG	2
15	JR1400	FFF	IT	1	34		12	Operations	HHH	2
16	JR1500	GGG	HR	1	35		13	Legal	AAA	2
17	JR1600	HHH	Finance	1	36		14	Customer Service	BBB	2
18	JR1700	AAA	IT	1	37		15	HR	CCC	2
19	JR1800	BBB	Operations	1	38		16	Finance	DDD	2
20	JR1900	CCC	Legal	1	39		17	Operations	EEE	2
21	JR2000	DDD	Customer Service	1	40		18	IT	FFF	2
22	JR2100	AAA	HR	2	5		19	HR	GGG	2
23	JR2200	BBB	Finance	2	6		20	Finance	HHH	2
24	JR2300	CCC	Operations	2	7		21	HR	AAA	1
25	JR2400	DDD	IT	2	8		22	Finance	BBB	1
26	JR2500	EEE	HR	2	9		23	Operations	CCC	1
27	JR2600	FFF	Finance	2	10		24	IT	DDD	1
28	JR2700	GGG	IT	2	11		25	HR	EEE	1
29	JR2800	HHH	Operations	2	12		26
30	JR2900	AAA	Legal	2	13		27
31	JR3000	BBB	Customer Service	2	14		28
32	JR3100	CCC	HR	2	15		29
33	JR3200	DDD	Finance	2	16		30
34	JR3300	EEE	Operations	2	17		31
35	JR3400	FFF	IT	2	18		32
36	JR3500	GGG	HR	2	19		33
37	JR3600	HHH	Finance	2	20		34
38	JR3700	AAA	HR	3	1		35
39	JR3800	BBB	Finance	3	2		36
40	JR3900	CCC	Operations	3	3		37
41	JR4000	DDD	IT	3	4		38
42
43
Sheet1

Cell Formulas
Range	Formula
D2:D41	D2	=COUNTIFS($B$2:B2,B2,$C$2:C2,C2)
E2:E41	E2	=RANK(D2,$D$2:$D$41,0)+COUNTIF($D$2:D2,D2)-1
H4:H41	H4	=IFNA(INDEX($B$2:$D$41,MATCH(1,((ROW()-3)=$E$2:$E$41)*($E$2:$E$41<=$I$2),0),2),"")
I4:I41	I4	=IFNA(INDEX($B$2:$D$41,MATCH(1,((ROW()-3)=$E$2:$E$41)*($E$2:$E$41<=$I$2),0),1),"")
J4:J41	J4	=IFNA(INDEX($B$2:$D$41,MATCH(1,((ROW()-3)=$E$2:$E$41)*($E$2:$E$41<=$I$2),0),3),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Another possibility.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Job Req ID	Previous company	Department	Countifs
2	JR100	AAA	HR	3		Top	25
3	JR200	BBB	Finance	3		Department	Company	Count of Job Req
4	JR300	CCC	Operations	3		HR	AAA	3
5	JR400	DDD	IT	3		Finance	BBB	3
6	JR500	EEE	HR	2		Operations	CCC	3
7	JR600	FFF	Finance	2		IT	DDD	3
8	JR700	GGG	IT	2		HR	EEE	2
9	JR800	HHH	Operations	2		Finance	FFF	2
10	JR900	AAA	Legal	2		IT	GGG	2
11	JR1000	BBB	Customer Service	2		Operations	HHH	2
12	JR1100	CCC	HR	2		Legal	AAA	2
13	JR1200	DDD	Finance	2		Customer Service	BBB	2
14	JR1300	EEE	Operations	2		HR	CCC	2
15	JR1400	FFF	IT	2		Finance	DDD	2
16	JR1500	GGG	HR	2		Operations	EEE	2
17	JR1600	HHH	Finance	2		IT	FFF	2
18	JR1700	AAA	IT	1		HR	GGG	2
19	JR1800	BBB	Operations	1		Finance	HHH	2
20	JR1900	CCC	Legal	1		IT	AAA	1
21	JR2000	DDD	Customer Service	1		Operations	BBB	1
22	JR2100	AAA	HR	3		Legal	CCC	1
23	JR2200	BBB	Finance	3		Customer Service	DDD	1
24	JR2300	CCC	Operations	3
25	JR2400	DDD	IT	3
26	JR2500	EEE	HR	2
27	JR2600	FFF	Finance	2
28	JR2700	GGG	IT	2
29	JR2800	HHH	Operations	2
30	JR2900	AAA	Legal	2
31	JR3000	BBB	Customer Service	2
32	JR3100	CCC	HR	2
33	JR3200	DDD	Finance	2
34	JR3300	EEE	Operations	2
35	JR3400	FFF	IT	2
36	JR3500	GGG	HR	2
37	JR3600	HHH	Finance	2
38	JR3700	AAA	HR	3
39	JR3800	BBB	Finance	3
40	JR3900	CCC	Operations	3
41	JR4000	DDD	IT	3
Master

Cell Formulas
Range	Formula
F4:H28	F4	=IFERROR(INDEX(SORT(UNIQUE(B2:D41),3,-1),SEQUENCE(G2),{2,1,3}),"")
D2:D41	D2	=COUNTIFS($B:B,B2,$C:C,C2)
Dynamic array formulas.

 

[alansidman]

Here is an alternative means by using Power Query. Mcode follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Req ID", type text}, {"Previous company", type text}, {"Department", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Department", "Previous company"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book2
	A	B	C
2	HR	AAA	3
3	Finance	BBB	3
4	Operations	CCC	3
5	IT	DDD	3
6	HR	EEE	2
7	Finance	FFF	2
8	IT	GGG	2
9	Operations	HHH	2
10	Legal	AAA	2
11	Customer Service	BBB	2
12	HR	CCC	2
13	Finance	DDD	2
14	Operations	EEE	2
15	IT	FFF	2
16	HR	GGG	2
17	Finance	HHH	2
18	IT	AAA	1
19	Operations	BBB	1
20	Legal	CCC	1
21	Customer Service	DDD	1
Table1

 

[zinah]

Try This:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Job Req ID	Previous company	Department	Countifs	Rank
2	JR100	AAA	HR	1	21			Top	25
3	JR200	BBB	Finance	1	22		No.	Department	Company	Count of Job Req
4	JR300	CCC	Operations	1	23		1	HR	AAA	3
5	JR400	DDD	IT	1	24		2	Finance	BBB	3
6	JR500	EEE	HR	1	25		3	Operations	CCC	3
7	JR600	FFF	Finance	1	26		4	IT	DDD	3
8	JR700	GGG	IT	1	27		5	HR	AAA	2
9	JR800	HHH	Operations	1	28		6	Finance	BBB	2
10	JR900	AAA	Legal	1	29		7	Operations	CCC	2
11	JR1000	BBB	Customer Service	1	30		8	IT	DDD	2
12	JR1100	CCC	HR	1	31		9	HR	EEE	2
13	JR1200	DDD	Finance	1	32		10	Finance	FFF	2
14	JR1300	EEE	Operations	1	33		11	IT	GGG	2
15	JR1400	FFF	IT	1	34		12	Operations	HHH	2
16	JR1500	GGG	HR	1	35		13	Legal	AAA	2
17	JR1600	HHH	Finance	1	36		14	Customer Service	BBB	2
18	JR1700	AAA	IT	1	37		15	HR	CCC	2
19	JR1800	BBB	Operations	1	38		16	Finance	DDD	2
20	JR1900	CCC	Legal	1	39		17	Operations	EEE	2
21	JR2000	DDD	Customer Service	1	40		18	IT	FFF	2
22	JR2100	AAA	HR	2	5		19	HR	GGG	2
23	JR2200	BBB	Finance	2	6		20	Finance	HHH	2
24	JR2300	CCC	Operations	2	7		21	HR	AAA	1
25	JR2400	DDD	IT	2	8		22	Finance	BBB	1
26	JR2500	EEE	HR	2	9		23	Operations	CCC	1
27	JR2600	FFF	Finance	2	10		24	IT	DDD	1
28	JR2700	GGG	IT	2	11		25	HR	EEE	1
29	JR2800	HHH	Operations	2	12		26
30	JR2900	AAA	Legal	2	13		27
31	JR3000	BBB	Customer Service	2	14		28
32	JR3100	CCC	HR	2	15		29
33	JR3200	DDD	Finance	2	16		30
34	JR3300	EEE	Operations	2	17		31
35	JR3400	FFF	IT	2	18		32
36	JR3500	GGG	HR	2	19		33
37	JR3600	HHH	Finance	2	20		34
38	JR3700	AAA	HR	3	1		35
39	JR3800	BBB	Finance	3	2		36
40	JR3900	CCC	Operations	3	3		37
41	JR4000	DDD	IT	3	4		38
42
43
Sheet1

Cell Formulas
Range	Formula
D2:D41	D2	=COUNTIFS($B$2:B2,B2,$C$2:C2,C2)
E2:E41	E2	=RANK(D2,$D$2:$D$41,0)+COUNTIF($D$2:D2,D2)-1
H4:H41	H4	=IFNA(INDEX($B$2:$D$41,MATCH(1,((ROW()-3)=$E$2:$E$41)*($E$2:$E$41<=$I$2),0),2),"")
I4:I41	I4	=IFNA(INDEX($B$2:$D$41,MATCH(1,((ROW()-3)=$E$2:$E$41)*($E$2:$E$41<=$I$2),0),1),"")
J4:J41	J4	=IFNA(INDEX($B$2:$D$41,MATCH(1,((ROW()-3)=$E$2:$E$41)*($E$2:$E$41<=$I$2),0),3),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you so much, I'm going to explore your formulas and see if they work, I really appreciate your prompt help!

 

[zinah]

Another possibility.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Job Req ID	Previous company	Department	Countifs
2	JR100	AAA	HR	3		Top	25
3	JR200	BBB	Finance	3		Department	Company	Count of Job Req
4	JR300	CCC	Operations	3		HR	AAA	3
5	JR400	DDD	IT	3		Finance	BBB	3
6	JR500	EEE	HR	2		Operations	CCC	3
7	JR600	FFF	Finance	2		IT	DDD	3
8	JR700	GGG	IT	2		HR	EEE	2
9	JR800	HHH	Operations	2		Finance	FFF	2
10	JR900	AAA	Legal	2		IT	GGG	2
11	JR1000	BBB	Customer Service	2		Operations	HHH	2
12	JR1100	CCC	HR	2		Legal	AAA	2
13	JR1200	DDD	Finance	2		Customer Service	BBB	2
14	JR1300	EEE	Operations	2		HR	CCC	2
15	JR1400	FFF	IT	2		Finance	DDD	2
16	JR1500	GGG	HR	2		Operations	EEE	2
17	JR1600	HHH	Finance	2		IT	FFF	2
18	JR1700	AAA	IT	1		HR	GGG	2
19	JR1800	BBB	Operations	1		Finance	HHH	2
20	JR1900	CCC	Legal	1		IT	AAA	1
21	JR2000	DDD	Customer Service	1		Operations	BBB	1
22	JR2100	AAA	HR	3		Legal	CCC	1
23	JR2200	BBB	Finance	3		Customer Service	DDD	1
24	JR2300	CCC	Operations	3
25	JR2400	DDD	IT	3
26	JR2500	EEE	HR	2
27	JR2600	FFF	Finance	2
28	JR2700	GGG	IT	2
29	JR2800	HHH	Operations	2
30	JR2900	AAA	Legal	2
31	JR3000	BBB	Customer Service	2
32	JR3100	CCC	HR	2
33	JR3200	DDD	Finance	2
34	JR3300	EEE	Operations	2
35	JR3400	FFF	IT	2
36	JR3500	GGG	HR	2
37	JR3600	HHH	Finance	2
38	JR3700	AAA	HR	3
39	JR3800	BBB	Finance	3
40	JR3900	CCC	Operations	3
41	JR4000	DDD	IT	3
Master

Cell Formulas
Range	Formula
F4:H28	F4	=IFERROR(INDEX(SORT(UNIQUE(B2:D41),3,-1),SEQUENCE(G2),{2,1,3}),"")
D2:D41	D2	=COUNTIFS($B:B,B2,$C:C,C2)
Dynamic array formulas.

Loved your approach it's the same approach that I'm looking for, using the cool formulas of office 365. The formulas worked for my example data, however, what if I have the columns not in the order of A, B, C, D, like below (G, BR,BA, and CL)?

G​	BR​	BA​	CL​
Job Req ID	Previous company​	Department​	Rank​

 

[zinah]

Here is an alternative means by using Power Query. Mcode follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Req ID", type text}, {"Previous company", type text}, {"Department", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Department", "Previous company"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book2
	A	B	C
2	HR	AAA	3
3	Finance	BBB	3
4	Operations	CCC	3
5	IT	DDD	3
6	HR	EEE	2
7	Finance	FFF	2
8	IT	GGG	2
9	Operations	HHH	2
10	Legal	AAA	2
11	Customer Service	BBB	2
12	HR	CCC	2
13	Finance	DDD	2
14	Operations	EEE	2
15	IT	FFF	2
16	HR	GGG	2
17	Finance	HHH	2
18	IT	AAA	1
19	Operations	BBB	1
20	Legal	CCC	1
21	Customer Service	DDD	1
Table1

Thank you so much for taking time and providing me with alternative approach, I'm going to test your approach on my real data and update you if I have any further questions.

 

[Fluff]

what if I have the columns not in the order of A, B, C, D, like below (G, BR,BA, and CL)?

You'ld probably be better off with power query or a macro.

 

[zinah]

You'ld probably be better off with power query or a macro.

I figured out a way to include the required columns that I mentioned earlier which is:

=IFERROR(INDEX(SORT(UNIQUE(B3:AN42),3,-1),SEQUENCE(AQ3),{1,18,39}),"")

But when I changed the columns from {2,1,3} to {1,18,39}, I need to change this part of formula as well (SORT(UNIQUE($BA:$CM),3,-1), however, not sure what should I replace "3" with? any recommendations?

Below is the results I got:

AQ3 is the top criteria which is 20 in my example below​
20​
		BBB​	Finance​	3​
		CCC​	Operations​	3​
		DDD​	IT​	3​
		EEE​	HR​	2​
		FFF​	Finance​	2​
		GGG​	IT​	2​
		HHH​	Operations​	2​
		AAA​	Legal​	2​
		BBB​	Customer Service​	2​
		CCC​	HR​	2​
		DDD​	Finance​	2​
		EEE​	Operations​	2​
		FFF​	IT​	2​
		GGG​	HR​	2​
		HHH​	Finance​	2​
		AAA​	IT​	1​
		BBB​	Operations​	1​
		CCC​	Legal​	1​
		DDD​	Customer Service​	1​
		AAA​	HR​	3​

 

[Fluff]

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