Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Top 15 on another sheet

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,947
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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.
    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
    Board Regular
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,947
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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!
    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  6. #6
    Board Regular
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,947
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Top 15 on another sheet

    Just change LARGE to SMALL in the first formula.
    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
    Board Regular
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,947
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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.
    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
    Board Regular
    Join Date
    Dec 2016
    Location
    London, United Kingdom
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

User Tag List

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com