Page 1 of 2 12 LastLast
Results 1 to 10 of 15
Like Tree6Likes

Top 15 on another sheet

This is a discussion on Top 15 on another sheet within the Excel Questions forums, part of the Question Forums category; Hey guys, Quick question. How would i go about getting a top 15 numbers from sheet 'pickrate' back to 'sheet1' ...

  1. #1
    New Member
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    51

    Default 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. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    4,346

    Default 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.
    LeeBillington likes this.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    51

    Default 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
    "-" 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?

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    4,346

    Default 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



    Glad to help!
    LeeBillington likes this.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    New Member mas123's Avatar
    Join Date
    Apr 2010
    Location
    Egypt
    Posts
    41

    Default Re: Top 15 on another sheet

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

  6. #6
    New Member
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    51

    Default Re: Top 15 on another sheet

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

  7. #7
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    4,346

    Default Re: Top 15 on another sheet

    Just change LARGE to SMALL in the first formula.
    mas123 and LeeBillington like this.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  8. #8
    New Member
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    51

    Default 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. #9
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    4,346

    Default 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.
    LeeBillington likes this.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  10. #10
    New Member
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    51

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com