Thanks:  0
Likes:  0

# Thread: Top 15 on another sheet

1. ## Top 15 on another sheet

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.

2. ## Re: Top 15 on another sheet

Try this:

A B
1 Top 15 Name
2 22 Luann
3 15 Ned
4 9 Fred
5 9 Mike
6 9 Penny
7 8 Ed
8 7 Joe
9 7 Olive
10 5 Cindy
11 5 Ike
12 4 Halley
13 3 Diane
14 2 Bob
15 2 Gayle
16 1 Al
Sheet1

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

Array Formulas
Cell Formula
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)))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

The formula in A2 is fairly simple, but the one in B2 is a little complicated to account for ties.

3. ## Re: Top 15 on another sheet

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

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?

4. ## Re: Top 15 on another sheet

Sure, just add the IFERROR like this:

AB
1Top 15Name
222Luann
315Ned
49Mike
59Penny
67Joe
77Olive
85Ike
94Halley
101Karen
11--
12--
13--
14--
15--
16--

Sheet1

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

Array Formulas
CellFormula
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))),"-")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

5. ## Re: Top 15 on another sheet

hello
you can use this formula in A2
Code:
`=iferror(LARGE(pickrate!\$K\$6:\$K\$1000,ROW(A1)),"")`

6. ## Re: Top 15 on another sheet

Thanks eric, what would i need to change for it to be bottom 15 also?

7. ## Re: Top 15 on another sheet

Just change LARGE to SMALL in the first formula.

8. ## Re: Top 15 on another sheet

For some reason the second array isn't working.. its just showing "-" in all cells.. even tho there is data!

9. ## Re: Top 15 on another sheet

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.

10. ## Re: Top 15 on another sheet

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•