HomeGoogle DocsSpreadsheetSORTN Tie Modes in Google Sheets - The Four Tiebreakers

SORTN Tie Modes in Google Sheets – The Four Tiebreakers

Published on

This post is dedicated to the SORTN Tie Modes in Google Sheets. There are 4 tiebreakers in SORTN which may be a confusing factor for many users. Let me simplify the use of Tie Modes in SORTN.

The function SORTN brings some unique data manipulation capability into Google Sheets. Don’t underestimate the power of this SORT function as it’s not simply for sorting. It’s more than that!

At the time of writing this post, as far as I know, SORTN is not part of any version of Excel including Office 365. The SORT and SORTBY are the available two SORT functions in Excel.

I have already tested Google Sheets SORTN function in the past and as a result, you can find some very rare tutorial on this site.

I was extensively using the display ties mode 2 in that posts because of its ability to handle duplicates in a data set. You can see that tutorials at the additional resources section at the bottom.

SORTN in Google Sheets

Suppose you have a data set with 250 rows. You can sort this data set using SORTN in ascending or descending order and return N number of rows.

Then why don’t you use Array_Constrain with SORT or Query to limit the rows in the sorted output?

The reason is the SORTN Tie Modes which are tiebreakers in case of duplicate rows. In this post, I am writing about the tie-modes in SORTN in Google Sheets.

basic difference of SORT and SORTN

For the past couple of months, I have been using the SORTN in a variety of formula combinations. So I think I can confidently write about the SORTN Tie Modes in Google Sheets.

The 4 SORTN Tie Modes in Google Sheets

There are 4 numbers of display ties mode that you can use in the SORTN. They are the # 0, 1, 2, and 3. Each SORTN tie modes has its own peculiarity.

SORTN Syntax:

SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, ...], [is_ascending2, ...])

SORTN Function Arguments:

Here I am only taking the arguments which are essential in learning the tie modes in this function. First, learn the ties mode, the rest you can easily grasp.

range – The data to sort.

n – the number of rows to return.

display_ties_mode – this you are going to learn in this post. The above two arguments are essential to learning SORTN tie modes in Google Sheets.

For more detail on arguments please refer to this SORTN Docs Editors Help.

For learning the SORTN Tie Modes in Google Sheets, I believe, there should be some unique kind of sample data.

So from my past experience in using SORTN, I’ve curated this sample data for you. It’s a demo data but worth to learn the SORTN function and the tie modes.

Best sample data to Learn SORTN in Google Doc Sheets

In this sample data set, there are total 9 rows and in which 6 rows are duplicates.

The rows highlighted in Blue and Green colors are the duplicates of the rows highlighted in Red.

I have done it purposefully to explain you about the SORTN tie mode tiebreakers. If there is not duplicate content, then there is no question of tiebreaking.

If you sort the first three rows, the result would be like this.

A 1 I
B 2 II
C 3 III

That means I have shuffled these three rows in the sample data. So that we can SORT it using SORTN.

These shuffled rows (in Red) copied and pasted twice below that. That’s all about the sample data that we are going to test with SORTN.

Let’s begin with the tie mode 0. Please understand that there is no tiebreak in this case. It simply ignores duplicates.

SORTN Display Ties Mode # 0

Sort and Return N Rows

What is the purpose of the display ties Mode 0 in Google Sheets SORTN?

The Display Ties Mode 0 is equal to the ARRAY_CONSTRAIN+SORT combo. Let me explain this with an example. The coming example is based on the sample data above.

Note: Refer my Google Sheets Functions Guide to learn any functions that mentioned in this post.

SORTN Formula:

=SORTN(A2:C,4,0)

This SORTN formula returns 4 rows after sorting the data.

ARRAY_CONSTRAIN+SORT Formula:

=ARRAY_CONSTRAIN(SORT(A2:C),4,3)

In this, the SORT formula sorts the data and the ARRAY_CONSTRAIN limits the sorted output to 4 rows and 3 columns. That means both produce the same output.

How to Use SORTN Tie Mode 0

The reverse of this is also possible! You can replace SORT with SORTN without the N number limitation. How?

Just replace the number of rows 4 with 9^9 as below. The 9^9 is equal to a large number 387420489. So virtually it means an unlimited number of rows.

=SORTN(A2:C,9^9,0)

This SORTN formula is equal to a plain SORT formula as below. Cool, right?

=SORT(A2:C)

SORTN Display Ties Mode # 1

Sort and Return N Rows + Additional Rows Identical to the Nth Row

What is the purpose of the display ties Mode 1 in Google Sheets SORTN? Read on to learn.

If you ask me which is the most confusing SORTN Tie Modes in Google Sheets, without any doubt I can say it’s the tie mode # 1.

It has one difference with display ties mode 0. It also sorts the dataset and returns N rows as per our earlier example. In the above example, the N is 4 (4 rows).

In the below example, additionally, the formula returns 2 more rows. There are total 6 rows in the output.

These additional rows are identical to the 4th row. Didn’t get?

display ties mode 1 in SORTN

This would obviously raise few questions in your mind.

Q. What happens if there are no duplicate rows in display ties mode 1?

A. The formula would return only n rows. There won’t be any additional rows.

Q. What would be the result of SORTN with tie mode 1, if I have the duplicates of the first row, not the nth row?

A. Again there would only be n number of rows. No additional rows would be in the output.

Here I think some additional examples are required. Let me take you to the conventional fruit data example.

The Sortn display ties mode 1 in detail

First, take a look at the data in column A and the sorted output with display ties mode 1 in C1:C.

The formula used in C1 is set to return 4 rows, plus any additional rows that are identical to the 4th row.

=sortn(A1:A,4,1)

The fourth row in the output is “Avocado”. Since there is no duplicate of this item, the SORTN with ties mode 1 limits the output to 4 rows.

But the second list in column E contains the item “Avocado” multiple times.  So the formula in G1 returns that repeated item the total number of times it repeats in the source.

=sortn(E1:E,4,1)

This is actually a tiebreaker! Suppose you want to pick the top ten scorers including ties. You can sort the data and limit the number of rows to 10.

In this case, you can use the SORTN display ties mode 0. But sometimes you may want to pick top 10 scorers that including ties + all the additional scorers with the same score of the 10th scorer. Here you can use the ties mode 1.

SORTN Tie Mode # 2

Sort and Unique (Advanced UNIQUE)

What is the purpose of the display ties Mode 2 in Google Sheets SORTN? In short, the answer is duplicate removal.

Among the SORTN Tie Modes in Google Sheets, this display ties mode 2 is the most useful one and special to me.

There is a distant similarity of the SORTN tiebreaker 2 formula with the UNIQUE formula.

The former (SORTN with ties mode # 2) returns the unique rows. Additionally, the output would be in sorted order. But the latter (UNIQUE) also returns the unique rows but without sorting.

The SORTN Formula in E2:

=SORTN(A2:C,9^9,2)

The UNIQUE formula in I2:

=UNIQUE(A2:C)

Rare comparison of SORTN ties mode 2 and UNIQUE

The SORTN function with ties mode # 2 eliminates duplicates in an advanced way. It surpasses the UNIQUE function in many ways. Here is one example.

The below sample data in the range A2: B contains the name of two different Hollywood films of which the titles are the same but released in different years.

I have purposefully repeated the first film that in cell A2 again in A6 to make it a duplicate entry.

advanced use of Ties Mode 2 in SORTN

As you can see there are three formulas in this screenshot. The UNIQUE formula in cell D2 and the SORTN in cell G2 return the same output.

Both the formulas eliminate the one and only duplicate in the source data, i.e. in A6: B6.

There is one more SORTN formula in cell J2 which deviates a little bit from what you have learned above.

Unlike the UNIQUE, when using the SORTN display ties mode #2, you can control which column to unique.

In the last formula, the # 1 after the tie mode #2 indicates the column number which decides the uniqueness of the row and the last 0 is the sort order.

SORTN Tie Mode # 3

Sort and Return N Rows + All Related Duplicates

What is the purpose of the display ties Mode 3 in Google Sheets SORTN?

The #3 is the last tiebreaker in SORTN Tie Modes in Google Sheets. Use this tiebreaker to return N rows plus all duplicates.

It’s quite opposite to the Tie Mode # 2. When using display ties mode # 2, the formula returns N rows after removing duplicates.

Here the formula would return N rows and include all the duplicates that related to the returned N rows.

What is SORTN Ties Mode 3

Here in this example, the number of rows to return, the so-called N, is 2.

If you use display ties mode 2 the formula would return the rows containing A and B, but only two unique rows.

Since the ties mode is 3, the formula returns two unique rows plus all duplicates of these two rows.

So I am winding up this tutorial on SORTN Tie Modes in Google Sheets. Hope you could learn the use clearly. Enjoy!

Additional Resources [SORTN Related]

1. Sort Data in Google Sheets – Different Functions and Sort Types.

2. Vlookup to Only Return Values from Max Rows in Google Sheets.

3. Find the Last Entry of Each Item from Date in Google Sheets.

4. How to Apply Unique in Selected Columns in Google Sheets.

5. How to Lookup Latest Dates in Google Sheets [Array Formula].

6. How to Find the Last Row in Each Group in Google Sheets.

7. Remove Duplicates in Google Sheets [The Complete Guide and Sample Sheet]

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here