Sorting Sorted (Sort of!)

This month, in our series of articles providing solutions to common issues encountered by finance professionals, we look at how to deal with sorting – both from a static and a dynamic perspective. By Liam Bastick, director (and Excel MVP) with SumProduct Pty Ltd.

Query

I am looking to sort text dynamically (i.e. as I amend the data, the sort will update automatically). I can’t seem to figure out how to do it. Any tips..?

Advice

As usual, I will refer to the attached Excel file when discussing sorting. Most users believe they fully understand data sorting but there are one or two “gotchas” lurking within Excel, which even the most seasoned pro may fall victim to from time to time. In its most basic form, the data to be sorted is already in a two-dimensional data table:

In the example above, the data table has already been converted to a Table with column headers (use CTRL + T to convert it to a Table; for more information on Tables, please refer to our article on Data Tables).  Creating Tables before sorting is definitely a safer approach for the uninitiated as a simple sort will ensure all columns are sorted rather than just the one by accident.

If you have created a Table, clicking on the drop down arrows in the headers will take you to sorting viz.

If you haven’t created a Table (or wish to use the Ribbon menu), highlight the range you wish to sort and then click on the ‘Sort’ button on the ‘Sort & Filter’ group on the ‘Data’ tab of the Ribbon:

The resulting drop down box is fairly self-explanatory:

Once satisfied with the sorting, simply click on ‘OK’ and voila!

So far, so good.  However, this type of sort is often referred to as a “static” sort.  As data is added, the sorted data will not update.  You need to perform the above actions once more.  Sometimes, you want the sorted data to update as data is amended / updated – this is known as a “dynamic” sort. 

I have shown previously how this may be easily done using the RANK function (please refer to this article) and how it may be applied in practice, e.g. in creating Tornado Charts (please see this article):

The problem with RANK is it only deals with numerical values.  Sometimes you want to sort non-numerical data too.

Consider the colours of the rainbow:

(Yes, you have read that last colour correctly!)  How can I sort this data alphabetically?  There are several alternatives:

RANK and SMALL (discussed in last month’s Insight article) generate #VALUE! and #NUM! errors respectively, as both are intended to be used with numerical data.  Surprisingly, COUNTIF – part of the COUNT family of functions designed primarily for use with numerical data – does work.  The formula

acts like a ranking function, rating the text in ascending order (for descending order, simply replace the less than sign (<) with the greater than sign (>)). 

Microsoft ranks data in the following ascending order:

with blank cells ranked last.  The descending order is simply the reverse of this list, with blanks still ranked last.  Hyphens and apostrophes tend to be ignored though, unless there are two similar text strings where one differs from the other by containing a hyphen: the hyphenated text string is ranked after the non-hyphenated text string.

In my example, then, the colours of the rainbow can be sorted accordingly:

The formula is given by:

This formula uses INDEX MATCH (please see >this article for further details) to re-order the data.  You should note the following:

·         SMALL(Range,n) has been used so that if there are duplicates, these will be included.  This seeks out the nth ranked item in the Range.  However, when using array functions, you often should use SMALL even when you do not require duplicates because some other functions do not work as you may wish when incorporated into array formulae

·         ROWS is simply used to create a counter to determine the value of n for SMALL to use

·         IFERROR has been used because you may note there are eight cells in the sorted range but only seven in the original one.  If you try to present results in more cells than you have data for, you will get the #NUM! error otherwise.

I am nearly there.  I simply need to add an array function (CTRL + SHIFT + ENTER, please see this article for more details) and conditions to allow for sorting data when it is not in a list and when you do not require blanks or duplicates.

The following is a comprehensive example.  Consider the following range of key Excel functions, cunningly given the range name Range (to create a range name, please refer to this article for more details):

This Range contains duplicates and blanks.  Moreover, it doesn’t have to be contiguous for the following formula to work:

Are you liking this formula?

It isn’t quite as bad as it looks.  It essentially extends the rainbow sorting example, but has to take into account that there is more than one row and more than one column in the Range table.  The array formula is required as calculations are done on more than one cell at a time (e.g. COUNTIF(Range,"<"&Range)+1).

This example is in the attached Excel file and I think you will agree it is a fairly comprehensive illustration of dynamic sorting!  (Why do I think the majority will simply copy this formula and use it as necessary..?)

Word to the Wise

Some readers may elect to traverse the VBA route for this problem, but it isn’t necessary.  VBA is not trusted by many end users and requires model users to enable macros (if that is allowed).  Regular readers will know I am always seeking formulaic alternatives!

Common issues to watch out for when sorting include: 

• Not selecting the correct range to sort in the first instance (this is particularly common when using Excel’s built-in ‘Sort’ functionalities and features
• Not appreciating the sorting order, especially with numbers that are not numerical.  For example, if you were to see the value 001234 in a cell, this must be text as the number would be stored as 1234.  Numbers are always sorted before text when a sort is in ascending order
• Always ensure there are no merged cells in the range to be sorted.  This may cause no end of issues!

If you have a query for the Spreadsheet Skills section, please feel free to drop Liam a line at liam.bastick@sumproduct.com or visit the SumProduct website.