+ Reply to Thread
Results 1 to 10 of 10

Ranking based on criteria

  1. #1
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Ranking based on criteria

    Is there a way to rank names based on a subcategory? And within the subcategory is there a way to rank those players as well.

    For example,

    I want to rank the following 5 subcategories in order based on rank and position. So for Rank 1, it would say C1, rank 2 it would say SP1, rank 3 would say C2, rank 4 would say C3 and rank 5 would say SP2.

    COL........A.,,...........B
    RW.......Rank........Subcat
    1...........1...............C
    2...........2..............SP
    3...........3...............C
    4...........4...............C
    5...........5,.............SP

    Thanks!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Ranking based on criteria

    Do you want to rank them or just sort them? Sorting based on B and then A will, i think, give you what you want?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Ranking based on criteria

    hi mlbdc. would be better if you can upload a sample excel file. say your data is:
    Data Range
    A
    B
    C
    1
    RW
    Rank
    Subcat
    2
    1
    1
    C
    3
    2
    2
    SP
    4
    3
    3
    C
    5
    4
    4
    C
    6
    5
    5
    SP
    =C2&COUNTIF(C$2:C2,C2)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: Ranking based on criteria

    Quote Originally Posted by FDibbins View Post
    Do you want to rank them or just sort them? Sorting based on B and then A will, i think, give you what you want?
    Ya I'm looking for it to physically same C2 or SP2 next to the letter. Thanks!

  5. #5
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: Ranking based on criteria

    Attached is my worksheet. Thanks!

    Rank Test.xlsx

  6. #6
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Ranking based on criteria

    The easiest way to do it is through pivot tables. On the attached I added two helper columns in "BLUE" to automatically calculate the subrank.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: Ranking based on criteria

    Quote Originally Posted by Xx7 View Post
    The easiest way to do it is through pivot tables. On the attached I added two helper columns in "BLUE" to automatically calculate the subrank.
    Is there anyway to do this without a helper column and just column E in your worksheet replace column C?

  8. #8
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: Ranking based on criteria

    Quote Originally Posted by Xx7 View Post
    The easiest way to do it is through pivot tables. On the attached I added two helper columns in "BLUE" to automatically calculate the subrank.
    I have no idea how pivot tables work, I wouldn't know where to even start.

  9. #9
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: Ranking based on criteria

    Quote Originally Posted by benishiryo View Post
    hi mlbdc. would be better if you can upload a sample excel file. say your data is:
    Data Range
    A
    B
    C
    1
    RW
    Rank
    Subcat
    2
    1
    1
    C
    3
    2
    2
    SP
    4
    3
    3
    C
    5
    4
    4
    C
    6
    5
    5
    SP
    =C2&COUNTIF(C$2:C2,C2)
    This works great! Thanks

  10. #10
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Ranking based on criteria

    Quote Originally Posted by mlbdc2012 View Post
    Is there anyway to do this without a helper column and just column E in your worksheet replace column C?
    Try this.. Use the filter to sort by subrank (eg. type in "C" to filter for all C1,C2,C3)
    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. [SOLVED] Ranking data based on set criteria
    By Panfergrrl18 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2014, 07:49 PM
  2. Ranking Based on Three Criteria
    By annie82 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-28-2013, 01:21 AM
  3. [SOLVED] Finding Nth ranking value based on multiple criteria
    By scaffdog845 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 04-26-2013, 01:00 PM
  4. Excel 2007 : Ranking Based on Three Criteria
    By CRIMEDOG in forum Excel General
    Replies: 4
    Last Post: 02-09-2012, 08:18 PM
  5. Ranking based on certain criteria?
    By Ahmad Adha Ali in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-18-2005, 04:51 AM

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