+ Reply to Thread
Results 1 to 7 of 7

Is it possible to identify Top 10 in a list with duplicates?

  1. #1
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Is it possible to identify Top 10 in a list with duplicates?

    I'm analyzing a large data set with about 100 lists, and I'd like a quick way of identifying the Top 10 within each list. Each list has company names in one column and whole numbers in the adjacent column, with occasional duplicate numbers. For instance:

    Company A - 140
    Company B - 152
    Company C - 140
    etc.

    I came across this explanation of using Large, Index, and Match to create a Top 10 list, but it doesn't work when the list has duplicate values. http://www.excel-user.com/2011/02/la...lues-from.html

    Do you guys know of a way to do this if your list has duplicate values?

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Is it possible to identify Top 10 in a list with duplicates?

    Hi kestefon,

    Large function can return top 10 with duplicates.. try exploring this and if stuck - upload a sample workbook.. thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Is it possible to identify Top 10 in a list with duplicates?

    Oops, I meant to attach it.

    DataExample1.xlsx

    Basically, I'd like to know what the Top 10 companies are based on "Index" for each of those attributes. (I've also included Unweighted since that has to be above 50 in order for the data to be usable, but I can probably evaluate that manually.) I have a huge data-set in that format (multiple sheets, each with dozens of attributes), and my ultimate goal is to be able to skim through the document and find every attribute where Company X lands in the Top 10.

    I'd like to be able to say something like, "Company A lands in the Top 10 for attributes X, Y, Z."

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Is it possible to identify Top 10 in a list with duplicates?

    Hi Kestefon,

    see the attached file and let me know if this helps:-

    DataExample1.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>

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

    Re: Is it possible to identify Top 10 in a list with duplicates?

    I did something similar to dilipandey except I used dummy columns to stay away from Array formulas

    In K3 for example copied down
    =D3&"_"&COUNTIF($D$3:D3,D3)
    K, L and M can be hidden after you set them up. These 3 columns give you unique identifiers for every multiple of a number, i.e 123_1 and 123_2.

    Then in P3 copied down
    =LARGE($D$3:$D$83,ROW(A1))
    In Q3 copied down
    =INDEX($A$3:$A$83,MATCH(P3&"_"&COUNTIF(P$3:P3,P3), $K$3:$K$83,0))
    You're matchinhg the number plus the times it has appeared on your top 10 list to the identifier in K
    See attachment
    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

  6. #6
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Is it possible to identify Top 10 in a list with duplicates?

    Amazing, thank you! For future reference, what did you do to deal with the possibility of duplicate values?

    EDIT: That was directed to dilipandey.

    Thanks to both you!

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Is it possible to identify Top 10 in a list with duplicates?

    Large function itself produced duplicate values..
    and basis that I used Index / rows/ if / countif etc to get next match if data is duplicate..


    Regards,
    DILIPandey
    <click on below * if this helps>

+ 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