Google Sheets has a killer function that’s not yet explored much. I am talking about the SORTN function. You can use it to apply the UNIQUE in selected columns in Google Sheets.

For example, in a two column dataset, you can apply the unique in the first column retaining the second column with the output. With the Unique function, it’s not possible.

It’s just like a Group By feature without any aggregation. For example, in a two column dataset that containing the item name in the first column and its sold quantity in the second column, you can group the column 1 without summing the column 2 values.

If you use the UNIQUE function in a two column dataset it would consider both the columns in the Unique evaluation. So the result would be undesirable.

Do you know how to use SORTN function in Google Sheets? If not just follow the below tutorial. If you miss this, you are going to miss a very powerful function similar to Query in Google Sheets.

Must Read: Google Sheets SORT Function – Usage and Examples

In this post, I am going to explaining to you how to use the TIE MODE 2 in SORTN. It makes SORTN an advanced form of UNIQUE function. You can overcome some of the UNIQUE function shortfalls using the TIE MODE 2 in SORTN.

How to Apply Unique in Selected Columns in Google Sheets

I am dealing this example section like a UNIQUE vs SORTN comparison. Please spend enough time to learn the below formulas. Because this can make you a master in removing duplicates in Google Sheets.

Must Read: Remove Duplicates in Google Sheets [The Complete Guide and Sample Sheet]

I am going to share you some very unique Google Sheets tips and tricks. I am sure that you have not come across these tips before. Let’s begin.

No doubt, as the name suggests, you can compare the SORTN function with the SORT function in Google Sheets.

But here, as I mentioned at the beginning of this post, we are only discussing the TIE MODE 2 in SORTN. What I am trying to say is when you use the Tie Mode 2 in SORTN, it behaves like an advanced UNIQUE function.

Unique One Column Using the UNIQUE Function

I have a one column dataset containing some fruits’ names. In that, the yellow highlighted fruit is repeated twice. The Unique formula in Cell C2 removes the second occurance of the Grapefruit.

The data now in Column C is Unique.

Unique One Column Using the SORTN Function

Similar to the UNIQUE formula above, you can unique one column data using a SORTN formula as below.

This formula also removes the extra Grapefruit. But this time the dataset in Column C is not only unique but also it’s sorted. That’s the only difference in the output.

If you compare both the formulas, other than the data range, you can see some additional elements in the SORTN.

1. 9^9 – You can use this as it is in any SORTN formula where the Tie mode 2 is used.

2. 2 – It’s the so-called tie mode number 2. You should use this number in SORTN formula to function it as Unique.

3. 1 – It’s the column number to unique. It’s flexible. You should change it when you have multiple columns. Instead of column number, you can also put the array (range) as A2: A10. In this example, we have only one column.

From the next example onwards, I am detailing you how to apply Unique in selected columns in Google Sheets. In that examples, I will explain to you how to use this column number.

4. TRUE – To SORT the column 1 in ascending order. You can put FALSE to sort in descending order. It has no effect on the unique evaluation. But it’s mandatory in the formula.

Unique Two Columns Using the UNIQUE Function

See this example.

This is a two column dataset. The first column contains the fruit names and the second column its price.

The Grapefruit has repeated three times in this dataset, that’s in row # 2, 10 and 11.

But the Unique formula considered the third time repeating of the Grapefruit in Row 11 as Unique because its quantity in column B is different. Let’s see how SORTN handles this situation.

Unique Two Columns Using the SORTN Function

Here I am sharing you a very worthy tip that you may not have seen elsewhere.

The above Unique formula considers both the columns A and B in the unique evaluation.

With SORTN you can either include both the columns in the unique calculation or any single column.

Here is that secret Google Sheets tip.

I am sharing you how to apply unique in selected columns in Google Sheets.

First I am considering both the columns in the Unique evaluation similar to the above Unique formula.

The result is same as the above Unique formula. The only difference is that the output is sorted here.

Should I explain the formula elements again here?

I think it’s not wise to repeat it again. You can find it just above, that under the earlier SORTN formula.

The difference is only in the point # 3, i.e. in the Sort Column usage. There I’ve used the number 1 as sort column. There I’ve also mentioned, you can use the column number as a range like A2: A11.

Here in this formula, I’ve used it as A2:A11&B2:B11. This’s a tricky use. This forces the formula to consider both the columns in the unique evaluation.

Actually, there is no need for us to use the SORTN formula in this case as the UNIQUE formula is sufficient to handle two column uniques.

But the point is, in SORTN you have the flexibility of using the selected column in unique.

See this example.

In this SORTN formula, I’ve only used column 1 (A2: A11) as sort column. So the formula retains the Grapefruit only one time.

Finally here is one more example. This time I am applying the SORTN in a three column dataset. Here I want to consider the column 1 and column 3 in the unique evaluation.

Unique Three Columns Using the SORTN Function

I am not going into any detail. The only change in the formula is in the sort column usage.

Conclusion

I am active in discussions related to Google Sheets online. From that, I could realize that even advanced Google Sheets users do not fully understand the potential of this function.

If you really want to become a Pro Google Sheets user, learn functions like SORTN and QUERY.