# Excel formula sorting data by date but group by column value

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

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):

And here it is:

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.

• 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