Getting the top 10 and bottom 10?

[rammi125]

I have a spreadsheet where column A has the company ticker, Column B the name, and Column C has their stock's change in performance. There are a total of 1000 rows of data, column C can sometimes be the same, for example two stocks could increase by 5%. Out of the 1000 companies I only care about 500 of them (I have a macro that will hide rows of the companies I do not own). On another tab I want to report the top 10 and bottom 10 performers (including ticker (col a) and name (Col B))based off of Column C for only the rows that are not hidden.

How would I best approach this problem? Using the rank/vlookup function? Conditional formatting? not sure the cleanest way or the actual formula to use, please help.

 

[cyrilbrd]

Hi, not sure if this is what you require:

Excel 2013
	A	B	C	D	E	F	G	H
1	ticker	name	stock			stock	ticker	name
2	gh8	q	492			911	gh89	g
3	gh4	w	236			795	gh101	f
4	gh5	e	51			761	gh48	y
5	gh1	r	476			761	gh27	u
6	gh7	t	159			681	gh62	h
7	gh48	y	761			677	gh81	o
8	gh27	u	761			492	gh8	q
9	gh2	i	380			476	gh1	r
10	gh81	o	677			402	gh21	k
11	gh9	p	321			380	gh2	i
12	gh18	l	375
13	gh21	k	402
14	gh10	j	242
15	gh62	h	681
16	gh89	g	911
17	gh101	f	795
rammi125

Cell Formulas
Range	Formula
F2	=LARGE($C$2:$C$17,ROWS(F$2:F2))
H2	=INDEX($B$2:$B$17,MATCH(G2,$A$2:$A$17,0))
G2	{=IFERROR(INDEX(A$2:A$17,SMALL(IF($F2=$C$2:$C$17,ROW($C$2:$C$17)-MIN(ROW($C$2:$C$17))+1,""),COUNTIF(F$2:F2,F2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[konew1]

As an alternative, you could just use autofilter
It will return top 10, but if there are multiple stocks with same score you will get more than 10 listed

 

[Aladin Akyurek]

@rammi125

Care to post a small sample of 10 to 15 rows?