Ignoring Blanks and get top 20

[zinah]

Hi,

I managed to use below formula to ignore blank and it worked, what I need now is how to get the top 20 or any top (any figure). I'm using below table as an example data:

Formula I used :

=UNIQUE(SORT(FILTER(Sheet1!$CQ$15:$CS$7241,Sheet1!$CR$15:$CR$7241<>""),3,-1))

Real data range in my file: Sheet1!$CQ$15:$CS$7241
The real count column range in my below example is: Sheet1!$CR$15:$CR$7241

Org​	Com​	Count​
CS​	CCC​	60​				Results
CS​	PIPPP​	48​				Top​	20​
IT​	Mercado Libre​	44​
CS​	SYSY​	35​
IT​	FireEye​	33​
CS​	WWW​	26​
IT​	OROOR​	24​					​
CS​	OROOR​	20​
CS​	OFOF​	20​
CS​	UNUN​	20​
IT​	ZZZ​	19​
CS​	FTFT​	18​
IT​	AAA​	17​
IT​	Globant​	16​
CS​	UUU​	16​
CS​	OOO​	15​
CS​	TTT​	15​
CS​	ZZZ​	15​
Finance​	AAA​	15​
CS​	DDD​	15​
HR​	AAA​	14​
IT​	Incluit​	14​
Finance​	CCC​	14​
IT​	Johnson Controls​	14​
CS​	VEVA​	14​
IT​	WWW​	13​
CS​	zScaler​	13​
CS​	AAA​	13​
IT​	SSS​	11​
CS​	Forescout​	11​
IT​	TTT​	11​
IT​	BlueVoyant​	11​
IT​	EEE​	11​
CS​	Tenable Systems​	10​
CS​	Nutanix​	10​
IT​	Tarjeta Naranja​	10​
HR​	RRR​	9​
Finance​	TTT​	9​
CS​	FireEye​	9​
IT​	CCC​	9​
CS​	Cylance​	8​
HR​	BBB​	8​
Finance​	LLL​	8​
CS​	SonicWall​	8​
CS​	QQQ​	8​
Finance​	Forescout​	8​
Finance​	WWW​	8​
Finance​	NetSkope​	8​
IT​	OFOF​	8​
IT​	KKK​	7​
CS​	Check Point​	7​
IT​	DDD​	7​
CS​	Quest​	7​

 

[Fluff]

You can do that the same way as you did here Top 25 Companies

 

[lrobbo314]

Try this

FYTD_App_by_Citizenship_T8_+_T42 (6).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Org	Com	Count			Results			Org	Com	Count
2	CS	CCC	60			Top	20		CS	CCC	60
3	CS	PIPPP	48						CS	PIPPP	48
4	IT	Mercado Libre	44						IT	Mercado Libre	44
5									CS	SYSY	35
6									IT	FireEye	33
7	CS	SYSY	35						CS	WWW	26
8	IT	FireEye	33						IT	OROOR	24
9	CS	WWW	26						CS	OROOR	20
10	IT	OROOR	24						CS	OFOF	20
11	CS	OROOR	20						CS	UNUN	20
12	CS	OFOF	20						IT	ZZZ	19
13	CS	UNUN	20						CS	FTFT	18
14	IT	ZZZ	19						IT	AAA	17
15	CS	FTFT	18						IT	Globant	16
16	IT	AAA	17						CS	UUU	16
17	IT	Globant	16						CS	OOO	15
18	CS	UUU	16						CS	TTT	15
19	CS	OOO	15						CS	ZZZ	15
20	CS	TTT	15						Finance	AAA	15
21	CS	ZZZ	15						CS	DDD	15
22	Finance	AAA	15
23	CS	DDD	15
24	HR	AAA	14
25	IT	Incluit	14
26	Finance	CCC	14
27	IT	Johnson Controls	14
28	CS	VEVA	14
29	IT	WWW	13
30	CS	zScaler	13
31	CS	AAA	13
32	IT	SSS	11
33	CS	Forescout	11
34	IT	TTT	11
35	IT	BlueVoyant	11
36	IT	EEE	11
37	CS	Tenable Systems	10
38	CS	Nutanix	10
39	IT	Tarjeta Naranja	10
40	HR	RRR	9
41	Finance	TTT	9
42	CS	FireEye	9
43	IT	CCC	9
44	CS	Cylance	8
45	HR	BBB	8
46	Finance	LLL	8
47	CS	SonicWall	8
48	CS	QQQ	8
49	Finance	Forescout	8
50	Finance	WWW	8
51	Finance	NetSkope	8
52	IT	OFOF	8
53	IT	KKK	7
54	CS	Check Point	7
55	IT	DDD	7
56	CS	Quest	7
Sheet6

Cell Formulas
Range	Formula
I2:K21	I2	=LET(tbl,FILTER(A2:C56,A2:A56<>"",""),top,UNIQUE(LARGE(INDEX(tbl,,3)+0,SEQUENCE(G2))),mtch, IF(ISNA(MATCH(INDEX(tbl,,3),top,0)),FALSE,TRUE),FILTER(tbl,mtch,""))
Dynamic array formulas.

 

[zinah]

Try this

FYTD_App_by_Citizenship_T8_+_T42 (6).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Org	Com	Count			Results			Org	Com	Count
2	CS	CCC	60			Top	20		CS	CCC	60
3	CS	PIPPP	48						CS	PIPPP	48
4	IT	Mercado Libre	44						IT	Mercado Libre	44
5									CS	SYSY	35
6									IT	FireEye	33
7	CS	SYSY	35						CS	WWW	26
8	IT	FireEye	33						IT	OROOR	24
9	CS	WWW	26						CS	OROOR	20
10	IT	OROOR	24						CS	OFOF	20
11	CS	OROOR	20						CS	UNUN	20
12	CS	OFOF	20						IT	ZZZ	19
13	CS	UNUN	20						CS	FTFT	18
14	IT	ZZZ	19						IT	AAA	17
15	CS	FTFT	18						IT	Globant	16
16	IT	AAA	17						CS	UUU	16
17	IT	Globant	16						CS	OOO	15
18	CS	UUU	16						CS	TTT	15
19	CS	OOO	15						CS	ZZZ	15
20	CS	TTT	15						Finance	AAA	15
21	CS	ZZZ	15						CS	DDD	15
22	Finance	AAA	15
23	CS	DDD	15
24	HR	AAA	14
25	IT	Incluit	14
26	Finance	CCC	14
27	IT	Johnson Controls	14
28	CS	VEVA	14
29	IT	WWW	13
30	CS	zScaler	13
31	CS	AAA	13
32	IT	SSS	11
33	CS	Forescout	11
34	IT	TTT	11
35	IT	BlueVoyant	11
36	IT	EEE	11
37	CS	Tenable Systems	10
38	CS	Nutanix	10
39	IT	Tarjeta Naranja	10
40	HR	RRR	9
41	Finance	TTT	9
42	CS	FireEye	9
43	IT	CCC	9
44	CS	Cylance	8
45	HR	BBB	8
46	Finance	LLL	8
47	CS	SonicWall	8
48	CS	QQQ	8
49	Finance	Forescout	8
50	Finance	WWW	8
51	Finance	NetSkope	8
52	IT	OFOF	8
53	IT	KKK	7
54	CS	Check Point	7
55	IT	DDD	7
56	CS	Quest	7
Sheet6

Cell Formulas
Range	Formula
I2:K21	I2	=LET(tbl,FILTER(A2:C56,A2:A56<>"",""),top,UNIQUE(LARGE(INDEX(tbl,,3)+0,SEQUENCE(G2))),mtch, IF(ISNA(MATCH(INDEX(tbl,,3),top,0)),FALSE,TRUE),FILTER(tbl,mtch,""))
Dynamic array formulas.

I reliaze that I don't have "LET" function in my Office 365 version any alternative solution?

 

[zinah]

You can do that the same way as you did here Top 25 Companies

The problem is I couldn't link this condition

=FILTER(Sheet1!$CQ$15:$CS$7241,Sheet1!$CR$15:$CR$7241<>"")

to my Top 25 formula

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))

 

[Fluff]

That's not the post I linked to.
Like

=INDEX(UNIQUE(SORT(FILTER($CQ$15:$CS$7241,$CR$15:$CR$7241<>""),3,-1)),SEQUENCE(CX16),{1,2,3})

 

[zinah]

That's not the post I linked to.
Like

=INDEX(UNIQUE(SORT(FILTER($CQ$15:$CS$7241,$CR$15:$CR$7241<>""),3,-1)),SEQUENCE(CX16),{1,2,3})

You ROCK!
I cannot thank you enough, it worked

 

[Fluff]

Glad we could help & thanks for the feedback.