# Group, sort and rank a list

1. ## 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!

2. ## 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

3. ## 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.

4. ## 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.

5. ## Re: Group, sort and rank a list

Thanks, works like a charm!

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

#### 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