+ Reply to Thread
Results 1 to 4 of 4

Ranking that allows and returns duplicate text values

  1. #1
    Registered User
    Join Date
    12-01-2011
    Location
    Rochdale, England
    MS-Off Ver
    Excel 2003
    Posts
    16

    Red face Ranking that allows and returns duplicate text values

    Hi guys,

    I want to create a list of values that updates automatically, dependent on a changing set of numbers.

    I have attached the workbook for your perusal.

    In the "Register" tab, people will be able to register which things they need to do by putting their name in an adjacent column. Further across, there is a formula that totals up how many names there are in that range. (B3:J77)

    Now, in the "List" tab, I want to create a list that shows which assignments are needed by the most people, sorted descending.

    I have tried using INDEX, SUMIF, and all sorts of RANK formulae. But I do feel a little bit "Woods for the trees". I couldn't get this to work when it comes across duplicate values.

    For instance, if 3 people need Assignment "Riding Shotgun" They should all have the same rank returned.

    If anyone could help me out, I would be massively grateful.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Ranking that allows and returns duplicate text values

    Put this formula in cell M3 on the Register worksheet and copy down:
    =RANK(K3,K$3:K$77)+COUNTIF(K$3:K3,K3)-1

    Put this formula in cell E2 on the List worksheet and copy down:
    =INDEX(Register!A:A,MATCH(D2,Register!M:M,0))

    Is that what you mean???
    If I've been of help, please hit the star

  3. #3
    Registered User
    Join Date
    12-01-2011
    Location
    Rochdale, England
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Ranking that allows and returns duplicate text values

    It works a treat man. Thank you very much!!

  4. #4
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Ranking that allows and returns duplicate text values

    No problem. Feel free to mark the thread as SOLVED if you're happy with that solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Ranking the same text values in a list
    By mljs54 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-01-2013, 09:39 AM
  2. Ranking list with duplicate values
    By joedrummer in forum Excel General
    Replies: 2
    Last Post: 06-21-2012, 04:13 PM
  3. [SOLVED] Ranking issue, returns #num! and #div/0! for a few rows
    By jagerstal in forum Excel General
    Replies: 2
    Last Post: 04-10-2012, 11:34 AM
  4. ranking duplicate values with secondary value
    By awaring in forum Excel General
    Replies: 6
    Last Post: 06-09-2010, 06:15 PM
  5. Replies: 6
    Last Post: 06-01-2010, 06:19 PM

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