+ Reply to Thread
Results 1 to 5 of 5

Group, sort and rank a list

  1. #1
    Registered User
    Join Date
    09-14-2012
    Location
    Oss, The Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    10

    Group, sort and rank a list

    I have a list of values that need to be grouped and sorted by date. ANd then I need to rank the groups. The first group should be the group with the earliest start date, the last with the latest start date. I have included a example with two worksheets. In the first worksheet you will find the current list and in the second worksheet you will find how I want the list to be grouped and ranked. Hope anyone can help me!
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,333

    Re: Group, sort and rank a list

    First - your dates are just texts, not dates. So select the column with "dates", use text-to-columns and select separated by tab and later on in the creator mark DMY format.
    Then explain why group 1 is 1 not 2. And give more detailed explanation what is used to identify which item belongs to each group. It is not #, because
    2nd, 4th, 5th, ... groups have the same # 40019055
    Last edited by Kaper; 03-12-2014 at 11:00 AM.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    09-14-2012
    Location
    Oss, The Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Group, sort and rank a list

    I guess that's the reason why I need a routine to do this because I'm not good at ranking myself... So thank you for pointing out that my list was incorrect. In my example 1 should be after 2. I have updated the sheet and also changed the formatting of the dates.
    Attached Files Attached Files

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,333

    Re: Group, sort and rank a list

    heh, I'd say there are just four groups.
    to find them and rank I'd do:
    in C3:
    =IF(A3<>A2,MAX(C$2:C2)+1,C2)
    in D3 array (Ctrl+Shift+Enter) comitted formula:
    =MIN(IF($C$3:$C$22=C3,$B$3:$B$22,""))
    to this point it is made in attachment

    after that (from situation in attachment):
    now select (from upperright to lowerleft corner) D3:A22 and sort ascending (in other word sort using column D as key)
    delete column D and your column C is now Rank.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-14-2012
    Location
    Oss, The Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Group, sort and rank a list

    Thanks, works like a charm!

+ 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: 2
    Last Post: 12-16-2013, 01:12 PM
  2. Replies: 3
    Last Post: 10-21-2013, 11:36 PM
  3. Replies: 0
    Last Post: 04-18-2013, 06:01 AM
  4. Max rank by group
    By klundtasaur in forum Excel General
    Replies: 4
    Last Post: 07-01-2011, 04:00 AM
  5. Replies: 0
    Last Post: 08-21-2006, 03:15 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