“SORT and SORTN are complicated, so I’ll explain UNIQUE first. In fact, there’s not really much to say about it, it just displays all rows in a specified range, deleting duplicates.
Inoue wrote the syntax on the whiteboard.
=UNIQUE(Range)
“So the function just removes all duplicate rows?”
“That is correct.”
“In Excel, I used to use the Filter function, that wasn’t really a function, just to display a list of unique values or names, but with this, you can use it even if the original data gets updated.”
This was very convenient. In Excel, there were many methods for extracting unique values. I was a fan of using the filter function to eliminate duplicates, but some people preferred copying and pasting the unique values they wanted into a pivot table. If this function existed though, it was definitely the correct answer to use it.
“I don’t think any further explanation is necessary. Now, let’s talk about SORT and SORTN. First of all, I would like you to understand that while these two functions look similar, they are completely different.”
“Huh?”
“This is what the syntax for each of them looks like.”
=SORT(Range, Sort Column, Ascending Order, [Sort Column 2, Ascending Order 2, …])
=SORTN(Range, [N], [Display mode for equivalent items], [Sort by Column 1, Ascending Order 1], …)
“Eh? Aren’t they the same? As their names suggest, they are both functions that sort and rearrange a given range before displaying it. SORTN just has more options, right?
“The N means to sort and display only the top N rows. The Display Mode for Equivalent Items is a list of options.
0: Sort the range and display the first N rows (or all rows if less than N rows).
1: Display the first N rows (or all rows if less than N rows), and display additional rows if they have the same value as the Nth row.
2: Display the first N rows (or all rows if less than N rows) after removing duplicate rows.
3: Display the first N unique rows (or all rows if less than N rows) and each duplicate row.
What was this. I didn’t get what they meant, it was too confusing. I stared at the whiteboard silently.
“It’s a bit complicated……I can kind of get options 0 and 1, but 2 is like a completely different function. If you’ve gone that far, you might as well just combine option 0 with UNIQUE. I also wonder when you would ever use option 3, I don’t think there’s many…No! In any case, you won’t be able to process it unless you memorise it, so please memorise it!”
“Okay. That’s what you meant when you said they were completely different.”
“No, that’s not the reason. The problem is specifying the sort column.”
What was the difference? It didn’t seem like it would be an issue.
“For example, if you wanted to sort B1:E20 by the value of column B, you would write SORT/SORTN like this.
=SORT(B1:E20, 2, TRUE)
=SORTN(B1:E20, 20, 0, B1:B20, TRUE)
Hm? What did that mean? I had no idea what was going on.
“It’s confusing. It can’t be helped.
Inoue spoke carefully.
“SORT specifies the column by column number, 1 for column A, 2 for column B. On the other hand, in SORTN, you specify the range itself to be sorted. NOte that writing B1:C20 does not mean the range will be applied in the order of B to C. The only things they have in common are the TRUE/FALSE options for ascending/descending order.”
“Why are there such different specification methods for functions that are so similar?”
“It’s a problem if you ask me. In any case, these are two completely different functions.”
What could I say. It might be even worse than SUMIF and SUMIFS.
“Looks like that’s all the explanations done. We’re here.”
Arriving at the quest sheet, Saito stopped the car.
※Functions
UNIQUE https://support.google.com/docs/answer/3093198
SORT https://support.google.com/docs/answer/3093150
SORTN https://support.google.com/docs/answer/7354624