+ Reply to Thread
Results 1 to 4 of 4

Replace all sheet values with their RANK

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    5

    Replace all sheet values with their RANK

    Attached is an excel file that contains all possible values in the first column (9238 of them) and a comma separated list of a subset of those values in the second column. I need to find a way to replace the smallest value with 1, the next smallest value with 2, ... , and the largest value with 9238 in every instance. In other words, I need their rank value. Is there a relatively easy way to do this? Thank you for any response.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Replace all sheet values with their RANK

    Hi,

    Clicking on the button on the worksheet should achieve what you're asking for I think.

    Hope this helps
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-16-2014
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Replace all sheet values with their RANK

    Thanks! I get an error with the macro on this line:
    Please Login or Register  to view this content.
    It's a run-time error '13': type mismatch. Also, interestingly, some of the values in the second column are being pasted together into single values.

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Replace all sheet values with their RANK

    The problem is that the final entry in row 1752 is 3903519, which does not exist as an entry in column A. There are 2 options:
    1. The data is erroneous, and correcting this will make the VBA error go away
    2. I can rewrite the code to cater for unmatched values. The attached workbook contains this solution. It will leave the number as is if a match isn't found
    Attached Files Attached Files

+ 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. Replies: 8
    Last Post: 02-20-2014, 07:03 PM
  2. RANK - Non unique values, contiguous rank required
    By PaulBo in forum Excel General
    Replies: 9
    Last Post: 02-20-2014, 10:34 AM
  3. NoobQ: Rank function treats negative values as positive values. Help!
    By lutonoodles in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-12-2013, 02:10 PM
  4. Formula to Rank and populate values base don Rank
    By Excel Dumbo in forum Excel General
    Replies: 3
    Last Post: 10-09-2012, 02:02 AM
  5. [SOLVED] RANK bug: same values get different ranks
    By Charles Blaquière in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

Tags for this Thread

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