How to Use SORTN Function in Google Sheets to Extract Sorted N Rows

0
109
How to Use SORTN Function in Google Sheets

I am very rarely using SORTN function in Google Sheets. I think Google Sheets SORTN function is not much popular like SORT. May be many of the Google Doc users are not aware of this function. This function is not available in Excel and so those who have made a switch from Excel to Google Sheets may be unaware of it.

I am using functions like Query and some times a combination of Unique and Sort as a replacement to SORTN. This function is useful for specific types of jobs like fetching rank holders from a long list and it’s not my cup of tea. But I advise you to learn how to use SORTN Function in Google Sheets. I personally of the opinion that this function deserves more attention than SORT as it’s more capable in filtering.

At some extent, you can use a SORTN formula in Google Sheets to replace the functions UNIQUE and Array_Constrain too. I’ll come to that at the later part of this tutorial.

Purpose of Google Sheets SORTN Function

The purpose of SORTN function is to return the first ‘n’ rows in a data set after performing a sort. The sort can be in ascending or descending order. So you can filter highest or lowest ‘n’ number of items from a range using SORTN. Further, you have better control over the output when there are duplicate entries in the range. You can decide whether to include or exclude duplicate rows.

Similar: Using Query how to extract top N Number of Items in Google Sheets

How to Use SORTN Function in Google Sheets

Though the syntax of SORTN function in Google Sheets seems little complex to understand, it’s not so in real sense. I hope I can well explain it with the help of few formulas. See below how to use SORTN formula.

Example to SORTN Function

Please note that, in the screenshot below, the sample data range is only A1:C6. After row 6, I’ve applied few SORTN formulas.

SORTN Formula Examples

Syntax:

SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], …)

Formula 1 in A8

=SORTN(A2:C6,2, 0 ,C2:C6,FALSE)

I have given colour pattern to make you understand the SORTN arguments. In this formula;

A2:C6 is the range, 2 [n] indicates the number of rows to return and 0 indicates the display ties mode. Now the balance is C2:C6 and that is the column to sort and you can use TRUE to sort in ascending or FALSE for descending order.

Display Tie Modes

There are four display ties mode in Google Sheets SORTN function. They are 0, 1, 2 and 3. In this formula 1, I’ve used display tie mode 0. What does it mean? Nothing concrete here. But other tie modes (tie mode 1, 2 and 3) have some purpose.

About formula output: This formula sorts the range A2:C6 based on the range C2:C6 in descending order and returns first two rows that has the highest value in Column C.

Formula 2 in A11

=SORTN(A2:C6,2,1,C2:C6,FALSE)

Note: In all the example formulas from 1 to 4 under proper titles, I’am using the same formula which I’ve used under the title “Formula 1 in A8”. The only change is the display tie mode. Here the tie mode is 1 and in the above example it was 0.

Please refer the screenshot above to see the results in A11. Here also the number of rows to return is 2. But the formula returns 3 rows. why?

This’s because of the use of display tie mode 1. I’m replicating the result again below for explanation purpose.

B 40 50
C 40 40
E 40 40

As I’ve mentioned, we have sorted the third column in descending order. So the formula returned the rows that containing the highest two values, i.e. 50 and 40, in the range plus one additional row. The additional row is identical to the second row in the formula result. It has the same value in Column C. 

In other words, player B and C tops if we select two persons as winners. But with this formula we can check if there are any other person with matching score of C.

What would be the result if there are two players with same top score. For example if B and C have the score 50, the formula would return these two rows only. There won’t be any additional rows. If B,C, and E have 50, the formula would extract these three rows. See the sentence in bold above to understand this.

Formula 3 in A15

Tie mode 2 use in SORTN Function in Google Sheets.

=SORTN(A2:C6,2,2,C2:C6,FALSE)

I’ve used tie mode 2 here. This formula returns 2 rows but after removing any duplicates. Here duplicates means not by comparing the entire rows but by comparing the values in the column range C2:C6. I think, in this case I should provide another example.

SORTN to remove duplicates in toppers

See the author’s name and book price. It’s just for example. It’s has not even any distant relation with the original price of any books of the author and I have not even given any book name.

Here a SORTN formula with tie mode 2 that is set to sort the column 2 containing the book price would return the following entries.

Agatha Christie $100.00
Agatha Christie $300.00

If the sorting column is one, that is the column that containing the author’s name, the result wold be as below.

Agatha Christie $100.00

Hope you could understand how SORTN eliminates duplicates.

Formula 4 in A18

This final formula shows tie mode 3 use in SORTN Function in Google Sheets.

=SORTN(A2:C6,2,3,C2:C6,FALSE)

Here is the use of tie mode 3. It’s almost similar to the above example formula 2. But our sample data in A2:C6 has not enough values to detail this formula. So I’m taking you to another example below.

A 40 50
B 40 50
C 40 40
D 40 25
E 40 40

Here I’ve changed the values in the column C2:C6. If this is the range in the above formula, the result would be as below.

A 40 50
B 40 50
C 40 40
E 40 40

You can see that the column C in the result contain two top values in that column plus additional two duplicates. In formula 2, it was one duplicate. But there the duplicate value should be matching to the second row value. Here there is no such condition. That’s all.

Hope you could learn how to use SORTN function in Google Sheets. I suggest you to make a sample sheet as above and apply all the formulas. You may change the values in Column C and see how that affect the results.

LEAVE A REPLY

Please enter your comment!
Please enter your name here