+ Reply to Thread
Results 1 to 5 of 5

Retrieving ranked data with ties

  1. #1
    Registered User
    Join Date
    03-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Retrieving ranked data with ties

    Hi,

    I have a number of work sites that are ranked from 1 to 1,000 based on a risk score. There are various data associated with each site and I want to pull select data out for each ranked site based on certain criteria.

    However, some of the ranked sites are tied. In the rankings column, it may show in chrono order: 1, 2, 3, 4, 5, 5, 5, 8, 9...

    In my worksheet where I am retrieving data I am using VLOOKUP which uses the ranking number (I've manually entered down a column) as the lookup_value. However, in the example above, I pull the information from the 5th ranked site, however, I get "#N/A" for the 6th and 7th ranked sites.

    Any suggestions on how to do this properly? I've used array based formulas in the past, but I'm a bit rusty with them (read alot!).

    Thanks to any that can provide some advice.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Retrieving ranked data with ties

    Would you consider changing the ranks? For example if you use this formula for ranking A2:A10 in row 2 copied down then all the ranks will be different

    =RANK(A2,A$2:A$10)+COUNTIF(A$2:A2,A2)-1

    then your VLOOKUP will always work
    Audere est facere

  3. #3
    Registered User
    Join Date
    03-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Retrieving ranked data with ties

    Perhaps. Are you able to explain what the countif function is doing? I have to run to a meeting but will check in later to see. Thanks! Appreciate the quick reply.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Retrieving ranked data with ties

    Where there are duplicates "scores" the COUNTIF will add a value that increases for each duplicate down the page, so if you have 4 scores toied for 1st place the first will be ranked 1, the second 2, the 3rd 3 etc.

    If you want to retain duplicate ranks you can still look up the values but the formulas required are a little more complex......

  5. #5
    Registered User
    Join Date
    03-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Retrieving ranked data with ties

    Great, thanks. I tried it and made some tweaks and all is working now. Thanks for your help!

+ 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. Extract ranked data from pivot
    By Langoo in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-26-2012, 08:25 AM
  2. Row and column labels of ranked data
    By Ulrich in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-31-2010, 10:39 AM
  3. Creating a ranked list on dynamic data.
    By dcgrove in forum Excel General
    Replies: 0
    Last Post: 09-21-2009, 05:08 PM
  4. [SOLVED] Data to be ranked Groupwise and then TOP 5 of Each Group to be pasted in other sheet.
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-12-2008, 08:55 AM
  5. Display ranked data in order from a range
    By S Davis in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-07-2006, 04:45 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