Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Morning people

I've been roped into helping someone with an excel sorting issue and I've got myself a bit stuck to be honest. I need to sort a spreadsheet by date (oldest to the newest) but group the data by groups.

So the date looks like this

ColumnA ColumnB ColumnC
1          15    2007-07-01
2          16    2007-08-12
3          16    2011-08-01
4          15    2001-01-02
5          17    2002-08-07
6          17    2012-04-02

So I need to sort the all the data by date but I need them also sorted in groups depending on the value in column be so all the groups should be together e.g.

  ColumnA ColumnB ColumnC
    4          15    2001-01-02
    1          15    2007-07-01
    2          16    2007-08-12
    3          16    2011-08-01
    5          17    2002-08-07
    6          17    2012-04-02

Whats the best way to tackle this?

Cheers

Edited:

ColumnA ColumnB ColumnC
    4          15    2001-01-02
    1          15    2007-07-01
    5          17    2002-08-07
    6          17    2012-04-02
    2          16    2007-08-12
    3          16    2011-08-01
share|improve this question

migrated from stackoverflow.com Mar 8 '13 at 13:09

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 2 down vote accepted

All you need is simple 2 columns sorting: Ribbon Data > Sort, add 2 levels of sorting as shown below (dates are formatted using my own regional settings, so don't worry about that):

2 cols sort

And here it is:

Result

UPDATE:

As per updated conditions, groups in column B should be sorted according to oldest date in group, while the groups are still stacked together. This may be achieved via intermediate calculation column. Type in D1 the following formula:

=MIN(IF($B1=$B:$B,$C:$C,2^100))

but press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in curly {} brackets around it (but do NOT type them manually!).

Then, perform sorting as suggested above, but with the following set of options:

Column D / Values / Smallest to Largest
Column C / Values / Oldest to Newest

This will result in desired output. After sorting column D may be removed.

share|improve this answer
    
Will this take into account the date as I need it to be sorted by date oldest but group columnB numbers together –  Grimlockz Mar 8 '13 at 9:57
    
@Grimlockz I don't understand exactly what you mean, but perhaps you may want to swap sorting order of columns. For the sample and expected result you provided the solution works as expected. –  Peter L. Mar 8 '13 at 9:59
    
Sorry I'm not being clear... Basically it has listed the ColumnB sequentially which keeps the groups together +the data within groups are sorted chronologically but the different groups arn't sorted chronologically you can see that they run almost identically to the columnA –  Grimlockz Mar 8 '13 at 10:08
    
but the different groups aren't sorted chronologically - what is the exact sorting criteria here? –  Peter L. Mar 8 '13 at 10:23
    
So basically after the sort has been done as above we then need to sort the data by date but keep all the groups together so use the oldest date for each of the groups - hope that makes sense –  Grimlockz Mar 8 '13 at 10:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.