+ Reply to Thread
Results 1 to 18 of 18

Rank data in variable range

  1. #1
    Registered User
    Join Date
    08-18-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Post Rank data in variable range

    Hi guys,

    I am completely stuck with this problem; any help would be much appreciated!

    So i want to do the following in VBA:

    Name Value Solution
    x 5 1
    x 64 3
    x 30 2
    y 19 1
    z 47 4
    z 36 2
    z 27 1
    z 36 2

    Obviously the number of names in the first column would vary from one day to another.
    Can you please help?

    Thanks!

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Rank data in variable range

    explain better please

  3. #3
    Registered User
    Join Date
    08-18-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rank data in variable range

    On column A i have a list of sorted names, some of which are repeated in various cells on the same column A.
    For each of the names in column A corresponds a number in column B.
    What i would like to do is to rank the numbers found in column B in column C. But the ranking has to be done only across the same names in column A.

    E.g
    A1=x, B1=20
    A2=x, B2=14
    A3=x, B3=39
    A4=y, B4=23
    A5=y, B5=17

    We should the ranking for the first three cells A1:A3 that all share the same name x, and a separate ranking for A4:A5 as they are equal to a different name (y). My difficulty is that the coordinates of the range are variable.

    Hope this helps.

    Thanks a lot!

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Rank data in variable range

    sorry, I don't understand, but you can attach your code (better your file), i'll arrange for variable range.

  5. #5
    Registered User
    Join Date
    08-18-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rank data in variable range

    wow, you are very kind, thank you!
    However i don't see how i can upload my file?

  6. #6
    Registered User
    Join Date
    08-18-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rank data in variable range

    With the attachment! Thanks
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Rank data in variable range

    I can't find any code in your workbook

  8. #8
    Registered User
    Join Date
    08-18-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rank data in variable range

    There's no code but the logic is in the formulas in column E

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

    Re: Rank data in variable range

    You can do this with a simple formula - in C2 copied down

    =COUNTIFS(A:A,A2,B:B,"<"&B2)+1
    Audere est facere

  10. #10
    Registered User
    Join Date
    08-18-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rank data in variable range

    Thanks for this, i would however prefer a solution in VB, as there is more code behind the sample sheet i provided

  11. #11
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Rank data in variable range

    Quote Originally Posted by petsek View Post
    My difficulty is that the coordinates of the range are variable.
    then you have code for fixed range
    Quote Originally Posted by patel45 View Post
    sorry, I don't understand, but you can attach your code (better your file), i'll arrange for variable range.
    do you want help me or not ?

  12. #12
    Registered User
    Join Date
    08-18-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rank data in variable range

    i did it for fixed range in the formula because i do no know how to do with variable range. Otherwise i would not be here

  13. #13
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Rank data in variable range

    It's very difficult to answer " I have no code but only formula"

  14. #14
    Registered User
    Join Date
    08-18-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rank data in variable range

    No probs. Thanks for your time and effort.

    Somebody else could take a look pls? thx

  15. #15
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Rank data in variable range

    Please Login or Register  to view this content.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Rank data in variable range

    Why not utilise daddylongleg's formula???
    Please Login or Register  to view this content.

  17. #17
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Rank data in variable range

    you are right, it's the simplest way

  18. #18
    Registered User
    Join Date
    08-18-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rank data in variable range

    Thank you so much for your help. This works brilliantly!
    I am so impressed with your willingness to help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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