Need to extract top 10 highest lines from a sheet.

[dommyo]

Where I work there are lines that are going out of stock more than once a week. We have recorded how many times they are going out of stock and I have to come up with a sheet that records the top 10 lines that are going out of stock the most frequently.

My spreadsheet is set out with unique ids on the left column and the amount of times it's been out of stock this week on the right. I have been trying everything to get it to work for the top 10 but I am struggling, I've only successfully used an index match to get the first occurring highest frequency value.

 

[AlanY]

how about?

Excel 2012
	A	B	C	D	E	F
1	ID	Out of Stock	Rank			Top10
2	ID1	37	13		1	ID13
3	ID2	50	7		2	ID10
4	ID3	41	11		3	ID4
5	ID4	75	3		4	ID14
6	ID5	42	10		5	ID11
7	ID6	48	8		6	ID19
8	ID7	46	9		7	ID2
9	ID8	6	16		8	ID6
10	ID9	3	18		9	ID7
11	ID10	78	2		10	ID5
12	ID11	54	5
13	ID12	4	17
14	ID13	84	1
15	ID14	72	4
16	ID15	7	15
17	ID16	1	20
18	ID17	3	18
19	ID18	33	14
20	ID19	53	6
21	ID20	38	12
Sheet1

Cell Formulas
Range	Formula
C2	=RANK.EQ(B2,$B$2:$B$21)
F2	=INDEX($A$2:$A$21,MATCH(E2,$C$2:$C$21,0))

 

[oldbrewer]

id1	3	3
id2	5	5
id3	7	7
id4	6	6
id5	1	1				the third col is number with a small different addition
id6	2	2				to resolve duplicate number issues
id7	0	0
id8	0	0
id9	4	4			col F
id10	6	6
id11	8	8				the top ten is
id12	3	3
id13	3	3		row 14	1	id17
id14	6	6			2	id11
id15	5	5			3	id19
id16	4	4			4	id3
id17	9	9			5	id14
id18	2	2			6	id10
id19	7	7			7	id4
id20	1	1			8	id15
					9	id2
					10	id16
					formula giving id17
					=OFFSET($C$1,MATCH(LARGE($C$2:$C$21,F14),$C$2:$C$21,0),-2)

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Where I work there are lines that are going out of stock more than once a week. We have recorded how many times they are going out of stock and I have to come up with a sheet that records the top 10 lines that are going out of stock the most frequently.

My spreadsheet is set out with unique ids on the left column and the amount of times it's been out of stock this week on the right. I have been trying everything to get it to work for the top 10 but I am struggling, I've only successfully used an index match to get the first occurring highest frequency value.

Some options...

1. Run a pivot table which allows for a Top 10 list.

2. Run a formula system. For this, try to adapt the following set up to your data:

http://www.mrexcel.com/forum/excel-questions/342541-top-5-numbers.html#post1690421

 

[BarryL]

Maybe try:

=INDEX(A:A,LARGE(B:B,ROW()-1))

In F2

 

[Aladin Akyurek]

Maybe try:

=INDEX(A:A,LARGE(B:B,ROW()-1))

In F2

Try yoiur suggestion on the data of the link I posted. Does it work as intended?

 

[BarryL]

Try yoiur suggestion on the data of the link I posted. Does it work as intended?

Nope hadn't thought of duplicate values. Apologies.