Top 15 on another sheet

LeeBillington

Board Regular
Joined
Dec 31, 2016
Messages
89
Hey guys,

Quick question.

How would i go about getting a top 15 numbers from sheet 'pickrate' back to 'sheet1' for example

So the numbers are in sheet 'Pickrates' between k6:k1000

but also how could i also get the name allocated to them top numbers in a different column? the names are in B6:B1000

both returning back to 'sheet1' from pickrate.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try this:

AB
1Top 15Name
222Luann
315Ned
49Fred
59Mike
69Penny
78Ed
87Joe
97Olive
105Cindy
115Ike
124Halley
133Diane
142Bob
152Gayle
161Al

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
A2=LARGE(pickrate!$K$6:$K$1000,ROWS($A$2:$A2))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B2{=INDEX(pickrate!$B$6:$B$1000,SMALL(IF(pickrate!$K$6:$K$1000=A2,ROW($K$6:$K$1000)-ROW($K$6)+1),COUNTIF($A$2:$A2,A2)))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



The formula in A2 is fairly simple, but the one in B2 is a little complicated to account for ties.
 
Upvote 0
Worked Perfectly thank you! but is there a way for the first one.. say if there is only 3 numbers "100" "200" "300" and no more.. that 4-5-6-7-8-9-10-11-12-13-14-15 place.. is a symbol like
"-" instead of "#NUM!"?

and same for the second one? but its just showing random names at the moment because there is no data for it to match to?
 
Upvote 0
Sure, just add the IFERROR like this:


Book1
AB
1Top 15Name
222Luann
315Ned
49Mike
59Penny
67Joe
77Olive
85Ike
94Halley
101Karen
11--
12--
13--
14--
15--
16--
Sheet1
Cell Formulas
RangeFormula
A2=IFERROR(LARGE(pickrate!$K$6:$K$1000,ROWS($A$2:$A2)),"-")
B2{=IFERROR(INDEX(pickrate!$B$6:$B$1000,SMALL(IF(pickrate!$K$6:$K$1000=A2,ROW($K$6:$K$1000)-ROW($K$6)+1),COUNTIF($A$2:$A2,A2))),"-")}
Press CTRL+SHIFT+ENTER to enter array formulas.


Glad to help!
 
Upvote 0
Did you enter the formula using Control+Shift+Enter? Next, check the ranges. If you still have issues, copy the formula and post it here so we can look at it.
 
Upvote 0
Never mind it worked, i ended up moving something and not accounting for that move so changed it slightly and it worked :)

but another question.. haha

how would i add to this formula.. so that if theres nothing there.. it will replace it with "-"

=IF(D7="","",VLOOKUP(D7,Register!$B$6:$D$401,2,FALSE))
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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