Need to extract top 10 highest lines from a sheet.

D

dommyo

New Member
Joined
Aug 5, 2016
Messages
1
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
how about?


Excel 2012
ABCDEF
1IDOut of StockRankTop10
2ID137131ID13
3ID25072ID10
4ID341113ID4
5ID47534ID14
6ID542105ID11
7ID64886ID19
8ID74697ID2
9ID86168ID6
10ID93189ID7
11ID1078210ID5
12ID11545
13ID12417
14ID13841
15ID14724
16ID15715
17ID16120
18ID17318
19ID183314
20ID19536
21ID203812
Sheet1
Cell Formulas
RangeFormula
C2=RANK.EQ(B2,$B$2:$B$21)
F2=INDEX($A$2:$A$21,MATCH(E2,$C$2:$C$21,0))
 
Last edited:
Upvote 0
[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]id1[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl22, align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id2[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl22, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id3[/TD]
[TD="align: right"]7[/TD]
[TD="class: xl22, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id4[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl22, align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id5[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl22, align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]the third col is number with a small different addition[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id6[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl22, align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]to resolve duplicate number issues[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id7[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl22, align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id8[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl22, align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id9[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl22, align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]col F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id10[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl22, align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id11[/TD]
[TD="align: right"]8[/TD]
[TD="class: xl22, align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]the top ten is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id12[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl22, align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id13[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl22, align: right"]3[/TD]
[TD][/TD]
[TD]row 14[/TD]
[TD="align: right"]1[/TD]
[TD]id17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id14[/TD]
[TD="align: right"]6[/TD]
[TD="class: xl22, align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]id11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id15[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl22, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]id19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id16[/TD]
[TD="align: right"]4[/TD]
[TD="class: xl22, align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]id3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id17[/TD]
[TD="align: right"]9[/TD]
[TD="class: xl22, align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]id14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id18[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl22, align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD]id10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id19[/TD]
[TD="align: right"]7[/TD]
[TD="class: xl22, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]id4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id20[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl22, align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD]id15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD]id2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD]id16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula giving id17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]=OFFSET($C$1,MATCH(LARGE($C$2:$C$21,F14),$C$2:$C$21,0),-2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
dommyo said:
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.
Click to expand...

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
 
Upvote 0
Maybe try:

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

In F2
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Stock count
Replies
1
Views
287
akmatz
A
D
  • Solved
Help with my Dynamic Top 10 Formula
Replies
7
Views
186
DanielJeff
D
S
  • Question Question
Algo help needed to find if a stock moved greater than 175% within a 5 day period over one year time
Replies
5
Views
402
offthelip
O
T
  • Solved
Return the top 10 occurrences of an item in a table and the count of that item
Replies
3
Views
628
Fluff
Fluff
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
417
Akuini
Akuini

Forum statistics

Threads
1,223,711
Messages
6,174,025
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top