Top 10 Extract
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Top 10 Extract

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a list of 50 or so products along with the units sold in the adjacent column. I would like to list the top 10 products along with the units sold at the bottom of this list. Can anyone help me with the code for this?

    thanks

    Matt

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you tried using the RANK worksheet function and then extract the top 10 or sort on the returned ranks?

    [ This Message was edited by: Mark W. on 2002-03-04 07:12 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,864
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-04 05:13, Matt wrote:
    I have a list of 50 or so products along with the units sold in the adjacent column. I would like to list the top 10 products along with the units sold at the bottom of this list. Can anyone help me with the code for this?

    thanks

    Matt
    Consider the following small sample in A1:B4.

    {"p1",20;"p2",20;"p3",30;"p4",15}

    In C1 enter:

    =RANK(B1,$B$1:$B$4,1)+COUNTIF($B$1:B1,B1)-1

    Copy down this as far as needed.

    In A53 enter:

    =INDEX($A$1:$A$4,MATCH(LARGE($C$1:$C$4,ROW()-52),$C$1:$C$4,0))

    Notice 52 in the formula, which is the number of rows before the cell of this formula!

    In B53 enter:

    =INDEX($B$1:$B$4,MATCH(LARGE($C$1:$C$4,ROW()-52),$C$1:$C$4,0))

    Select A53:B53 and copy down the last two formulas as far as needed: In your case, 10 rows down.

    Aladin

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Aladin

    That's great, thanks for your help!

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
  •  

 

 
DMCA.com