Are you ready to unlock the full power of SORTN in Google Sheets? If you’ve ever been confused by the four tiebreakers in SORTN, fear not! In this article, I’ll simplify the use of Tie Modes in SORTN and show you how to make the most of this powerful function.
SORTN is not your average sorting function. It brings a unique data manipulation capability to Google Sheets that goes beyond simple sorting. And while SORTN is not available in Excel or Office 365, it’s definitely worth exploring if you want to level up your spreadsheet game.
As a senior Google Sheets expert, I’ve extensively used SORTN in various formula combinations. I’ve tested it, tinkered with it, and discovered its hidden secrets. So sit back, relax, and let me guide you through the world of SORTN Tie Modes.
SORTN in Google Sheets
Before we dive into the tiebreakers, let’s quickly recap what SORTN is all about. Suppose you have a dataset with 250 rows, and you want to sort it in ascending or descending order, returning a specific number of rows. This is where SORTN comes in handy. You can use it to sort the data and limit the output to N number of rows.
Now, you might be wondering why you can’t achieve the same result using other functions like Array_Constrain with SORT or Query. Well, that’s where the Tie Modes come into play. These tiebreakers determine what happens in case of duplicate rows, and they add an extra layer of flexibility to SORTN.
The 4 SORTN Tie Modes in Google Sheets
Let’s get down to business and explore the four Tie Modes in SORTN. Each Tie Mode has its own unique characteristics. Here’s a breakdown of what each Tie Mode does:
Tie Mode 0: Sort and Return N Rows
Tie Mode 0 is the simplest of the bunch. With this Tie Mode, SORTN ignores duplicates and simply returns N number of rows after sorting the data. It’s like using the ARRAY_CONSTRAIN+SORT combo. For example:
=SORTN(A2:C, 4, 0)This formula sorts the data and returns the top 4 rows. Alternatively, you can achieve the same result using:
=ARRAY_CONSTRAIN(SORT(A2:C), 4, 3)Both formulas produce the same output. You can also replace the number of rows (4) with a large number like 9^9 to virtually mean an unlimited number of rows.
Tie Mode 1: Sort and Return N Rows + Additional Rows Identical to the Nth Row
Tie Mode 1 is where things get a bit more interesting. This Tie Mode not only sorts the dataset and returns N rows, but it also includes additional rows that are identical to the Nth row. Let’s take a look at an example:
=SORTN(A1:A, 4, 1)In this case, the formula returns 4 rows, plus any additional rows that are identical to the 4th row. If there are no duplicate rows or if the duplicates are not related to the Nth row, the formula will only return N rows.
Tie Mode 2: Sort and Unique (Advanced UNIQUE)
Tie Mode 2 is a powerful tool for removing duplicates. It returns unique rows in sorted order, combining the functionality of SORTN with the advanced uniqueness of the UNIQUE function. Here’s an example:
=SORTN(A2:C, 9^9, 2)This formula eliminates duplicates in an advanced way, surpassing the simplicity of the UNIQUE function. It returns unique rows in sorted order, making it incredibly useful for data manipulation.
Tie Mode 3: Sort and Return N Rows + All Related Duplicates
What if you want to not only return N rows but also include all duplicates related to those rows? That’s where Tie Mode 3 comes in. It’s the opposite of Tie Mode 2. Instead of removing duplicates, it includes them in the output. For example:
=SORTN(A2:C, 2, 3)This formula returns N rows and includes all duplicates that are related to the returned N rows. It’s perfect for situations where you want to analyze specific rows and their related duplicates.
And there you have it – the four Tie Modes in SORTN that will take your data manipulation to the next level. With these tiebreakers in your arsenal, you’ll be able to tackle even the most complex sorting tasks.
But wait, there’s more! If you want to dive deeper into SORTN and explore related topics, I’ve curated a list of additional resources for you:
- Sort Data in Google Sheets – Different Functions and Sort Types
- Vlookup to Only Return Values from Max Rows in Google Sheets
- Find the Last Entry of Each Item from Date in Google Sheets
- How to Apply Unique in Selected Columns in Google Sheets
- How to Lookup Latest Dates in Google Sheets [Array Formula]
- How to Find the Last Row in Each Group in Google Sheets
- Remove Duplicates in Google Sheets [The Complete Guide and Sample Sheet]
So go ahead and explore the world of SORTN Tie Modes. Have fun sorting, manipulating, and uncovering hidden patterns in your data using this powerful function. And remember, if you need more Google Sheets tips and tricks, visit Crawlan.com – your go-to resource for all things Google Sheets!
