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!
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
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.
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.
Thanks, works like a charm!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks