+ Reply to Thread
Results 1 to 4 of 4

How to identify the top 3 for each category

  1. #1
    Registered User
    Join Date
    07-15-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    18

    How to identify the top 3 for each category

    Friends,

    I need this help! I have this huge database and I am attaching a snap shot of it with this. I need to find the top 3 for each category..and there are thousand of category in this database.(using Excel 2007). kindly someone guide me on how to do this in automated way(Using formulas). I cannot manually do a group by categories so the utility must be intelligent enough to identify that the next row is with the new category ..so search for top three within the range.

    category customer
    0G34 a 200 2010-W20
    0G34 a 4 2010-W20
    0G34 a 264 2010-W20
    0G34 a 167 2010-W20
    0G34 a 3 2010-W20
    0G54 b 2 2010-W20
    0G60 c 30 2010-W20
    0G60 c 10 2010-W20
    0G60 c 80 2010-W20
    0G60 c 1 2010-W20
    0G62 e 150 2010-W20
    0G62 e 2 2010-W20
    0G62 e 180 2010-W20
    0G62 e 370 2010-W20
    0J11 f 150 2010-W20
    0J11 f 1400 2010-W20
    0J11 f 50 2010-W20
    0J11 f 3 2010-W20
    0J11 f 30 2010-W20
    0J14 g 20 2010-W20
    0J14 g 300 2010-W20
    0J14 g 40 2010-W20
    0J14 g 45 2010-W20
    0J14 g 235 2010-W20
    0J14 g 1990 2010-W20



    Expected Result of top 3 customers for each category

    category Top Second Third
    0G34 a 264 200 167
    0G54 b 2 NA NA
    0G60 c 80 30 10
    0G62 e 370 180 150
    0J11 f 1400 150 30
    0J14 g 1990 300 235


    Thanks,
    Raj

  2. #2
    Registered User
    Join Date
    07-15-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: How to identify the top 3 for each category

    Just to repost with the excel file ...if in case the last post is not clear..Apologies for duplication ..I am new o this forum ..

    Kindly find the excel file with this ..where I am trying to find the top 3 customers for each categories(the categories are in thousands)

    Thanks,
    Raj
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How to identify the top 3 for each category

    Okay, in your example, in G3 dragged down
    =VLOOKUP(F3,$A$3:$B$27, 2,)

    In H3 entered as an Array (CNTRL SHIFT ENTER)
    =IFERROR(LARGE(IF($A$3:$A$27=$F3,$C$3:$C$27,""),COLUMN(A1)),"N/A")
    (IFERROR only works with 2007 and up. If you want compatability with 2003, you'll need to use an IF with ISERROR to get your "N/A"s.
    See attachment.
    Does that work for you?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    07-15-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: How to identify the top 3 for each category

    Thanks chief! ..it helps !

    Regards,
    Raj






    Quote Originally Posted by ChemistB View Post
    Okay, in your example, in G3 dragged down
    =VLOOKUP(F3,$A$3:$B$27, 2,)

    In H3 entered as an Array (CNTRL SHIFT ENTER)
    =IFERROR(LARGE(IF($A$3:$A$27=$F3,$C$3:$C$27,""),COLUMN(A1)),"N/A")
    (IFERROR only works with 2007 and up. If you want compatability with 2003, you'll need to use an IF with ISERROR to get your "N/A"s.
    See attachment.
    Does that work for you?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1