This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Find out how dynamic arrays in Excel will change your (working) life! Part four of a six-part series of blogs
Dynamic arrays are a new feature being rolled out in Excel 365. This blog explains how they work, and why they will make so many things in Excel easier. Using dynamic arrays in Excel Can I use dynamic arrays? Introducing dynamic arrays Filtering and sorting data (this blog) Two more functions: RANDARRAY and SEQUENCE Implications of dynamic arrays

Find out how dynamic arrays in Excel will change your (working) life!

Part four of a six-part series of blogs

Dynamic arrays are a new feature being rolled out in Excel 365. This blog explains how they work, and why they will make so many things in Excel easier.

Using dynamic arrays in Excel
Can I use dynamic arrays?
Introducing dynamic arrays
Filtering and sorting data (this blog)
Two more functions: RANDARRAY and SEQUENCE
Implications of dynamic arrays

Posted by Andy Brown on 14 February 2020

In this blog

Picking out unique values
Sorting values
Sorting one set of values by another
Filtering data

Filtering and sorting data

For this page, assume that I've created three range names called House, Character and Rating (shown coloured separately below):

You can do this by selecting A1:C11, pressing SHIFT + CTRL + F3 and choosing Top row.

Picking out unique values

The new UNIQUE function takes the following 3 arguments:

Argument	Type	Use
1	Compulsory	The block of cells from which you want to pick out unique values.
2	Optional	Whether you want to pick out unique rows (in the selected range above there aren't any, since the combination of each character's house, name and rating is unique) or unique values.
3	Optional	Whether you want to pick out values which appear exactly once (in the above example there aren't any, as each house has at least two values).

Here's an example to pick out the unique Hogwarts' houses:

If you miss out the second and third arguments, you'll get one version of each house referenced.

When you enter the above formula, you'll get this:

The function creates a spill range containing (as things stand) 4 entries.

Sorting values

You can use the SORT function to sort dynamic arrays. It takes these arguments:

Argument	Type	Use
1	Compulsory	The column or range you want to sort.
2	Optional	The column number in this range you want to sort by.
3	Optional	The sort order (1 is ascending, -1 is descending).
4	Optional	Whether you are sorting by column or by row.

For our example we'd like the houses to appear in alphabetical (and, coincidentally, moral) order:

Here we're sorting the house names alphabetically (the default). The formula is =SORT(UNIQUE(House)).

Sorting one set of values by another

Supposing you now decide that you want to sort the characters according to the rating given. To do this, you can use the SORTBY function, which takes these arguments:

Argument	Type	Use
1	Compulsory	The column or range you're sorting
2	Compulsory	The values you're sorting by
3	Optional	The sort order (1 is ascending, -1 is descending).
4	Optional	Additional array and sort order pairs (allowing you to create additional sort keys).

Here's an example of a SORTBY function in use:

This formula is sorting the Harry Potter characters into descending order by their rating. Luna Lovegood has the highest rating, so comes at the top of the list.

Typically you would use the SORTBY function for sorting tables of data and the SORT function for sorting single columns.

Filtering data

You can also use dynamic arrays to pick out the rows you want, using the FILTER function. This takes the following arguments:

Argument	Type	Use
1	Compulsory	The array to be filtered
2	Compulsory	An expression to evaluate for each row
3	Optional	What to return if the filter would return no data

So for our example, you could show all the people in Slytherin (hiss!):

This formula would filter the list of characters to pick out all the ones in Slytherin.

Here's what this would show:

The two people in Slytherin returned by the FILTER function.

If you look for the people in the Treetops house, you'll get an error:

If a FILTER function returns no data, you get a #CALC! error.

You can get round this by using the third argument:

You can use the third argument to return something in the cell containing your formula.

You can combine two or more filters like this:

This function returns the characters in Gryffindor house who have a Wise Owl rating of 8 or more.

The syntax for this seems a bit ugly to me - I wonder why you can't include extra filters as additional arguments?

Parts of this blog
Using dynamic arrays in Excel Can I use dynamic arrays? Introducing dynamic arrays Filtering and sorting data (this blog) Two more functions: RANDARRAY and SEQUENCE Implications of dynamic arrays

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

ALL BLOGS

EXCEL BLOGS

EXCEL BLOGS

Filtering and sorting data

Picking out unique values

Sorting values

Sorting one set of values by another

Filtering data

Head office

London

Manchester