Creating First in first out and last in last out Inventory management can be tedious job but with SortN function we can make it easy. So with the function we will sort items. We will create a Stock Management Spreadsheet template to manage inventory where we would be able to see items or products that we have received recently and products that we have received earlier. With the function we can limit the number of entries, columns we want to sort and then decide if you want them in ascending or descending order, and many such things. Also you can skip duplicates or ties with the Display Ties Mode type.
Let’s say there are entries of clothes and you want to display a shopping window for the latest arrivals and clearance sales. So based on the SortN function and ascending descending feature we can sort the list and determine what are the items that can go in the “Clearance Sale” and “Latest Arrivals”. It could be like a seasonal sale, like the “Latest Arrivals” in the summer sale as the latest arrivals and in “Clearance Sales” we will have a winter collection that we failed to sell in the winter season.
Let’s break down the formula SortN sorts the range and limit it to the Nth position. N means it’s the number where we want to stop the formula and there could be some exceptions which we can give with display ties mode.
So it starts with SORTN and then the first argument is range which is mandatory. When we select the range, it will sort the first column and return one result by sorting it in ascending order. By default it’s set on 1 or we can say we have to set it greater than or equal to 1. It can’t be 0 or less than 1.
You can see the example image below.
Let’s add the next argument that is N. It’s the number of rows we want to return. In this example let’s say we want 5.
Now you can see we have 5 items sorted in ascending order.
Let’s check out our next argument i.e. display ties mode. It starts from 0 or false. Let’s learn them one by one, so we will start from 0. When we see the result nothing changes because Display Ties Mode 0 is default mode. It sorts only Nth rows, and doesn’t show extra entries if there are ties on the Nth position. But keeps ties if they are not on the Nth position. In below example there is tie on 5th position but SortN showing only 5.
The next Display Ties Mode 1, is opposite of 0 or False. It shows rows that have ties on the Nth position.
As you can see in the image below.
Mode 2 does the opposite of 1 and 2, it removes ties completely from the sort result.
See in the image below.
Last mode i.e. 3, allows ties for each unique value till the Nth unique value.
See in the image below.
Now we know how SortN function works. Let’s use this function with Primary and Secondary Sort. In our example we wanted to sort Latest Arrivals based on received date and discount. So received date would be our primary sort and discount would be secondary sort. These are recently arrived items with highest discount.
You can see it in the following image.
With SortN function you can easily sort you inventory and make your sorting process more efficient. I’ve created a video to show SortN function with an example.
