Today, we want to learn about sorting by months with the SORT function.
SORT by months using functions
Let’s start with writing the SORT function, with an array as the first argument. In the second argument we need to write 2, as we want to sort by months, which are located in the second column. Now, we can close the function (Fig. 1).
=SORT(A2:C145,2)
Fig. 1 SORT function
Just like that the SORT function sorted data by the Month column. It’s important that the SORT function doesn’t consider the custom list. Excel does it, but the SORT function doesn’t. Now. let’s have a look at how we can properly sort by months using the SORT function. First of all, we cannot use the SORT function, but the SORTBY function. In this function, even if we change the second argument to a month column, we still get an ascending sorting, from A to Z. It means that the sorting is based on the alphabet, not on months of the year. The thing is we cannot do the sorting based on arguments. We need to use the MATCH function. It will look up a given month in a list of months. In the third argument we write 0, as we want to have the exact match (Fig. 2)
=MATCH(B2,$J$2:$J$13,0)
Fig. 2 MATCH function
The MATCH function starts the sorting from the top on the Month column. As we can see in column D, we have a proper number for each month. The column acts as a helper column, however, sometimes we don’t want any helper column, so we can create a helper column in our formula. We can match each month from the Month column (column B) using the MATCH function, where we need to write the lookup value in the first argument, which is our data column. In the second argument, we need to write the whole list of months on the right, then let’s write 0 as the exact match (Fig. 3)
SORTBY(A2:C145,MATCH(B2:B145,J2:J13,0))
Fig. 3 SORTBY function
It works. We see that the SORTBY function found a proper numbers for each month. It’s important that it sorts by months, not by years. However, we can add the year as well. But, I want to show you something else. First of all, if we don’t like the custom helper column list, we can just click F9 in the lookup array argument to hard code the array list in our formula (Fig. 4)
We can even delete the custom column. As we can see, it works properly. When we look at column D, we can see #N/A error. It’s because the MATCH function is trying to find months in the custom column cells which are empty (Fig. 5)
=MATCH(B2,$J$2:$J$13,0)
Fig. 5 Empty cells in the Month column
Now, let’s focus on dates based on years and months. Sometimes, our date in a dataset isn’t written only in one column, but it can be separated into the year, month and day columns. We can create a full date from the data we have. We will try it in the Helper column. First of all, we write =, then 1 combined with a month, combined with a year (Fig. 6)
=1&B2&A2
Fig. 6 Creating a date
And just like that, we combined the 1st of January 2022. Our date, however, is text. We want Excel to understand it as a date, so we just need to add parentheses and a 0 (Fig. 7)
=(1&B2&A2)+0
Fig. 7 Changing text into numbers
Excel magic has just happened! It changed text into numbers. Now, to change the numbers into dates, we need to go to the Home tab (1), then change the formatting (2) into Short Date (3) (Fig. 8)
Fig. 8 Numbers into a date
Since we have dates based on the year and the month columns, we can sort them. Let’s write the SORTBY function. In the first argument we write the data column, and in the second column we have to combine 1 with the Month column, then with the Year column. When we close the parentheses we need to add 0 to change text values into numbers (Fig. 9)
=SORTBY(A2:C145,(1&B2:B145&A2:A145)+0)
Fig. 9 SORTBY function
Now, we have only six Januaries at the beginning, because we have have only six Januaries in 2022. Next six Januaries are in 2023. This way we sorted our data by months and years using a helper column.
Today, we are going to talk about choosing a random element from a list without repeats. We have eight elements in our list. I put there the same number two times, which gives me the opportunity to choose the same number two times. If there are three the same numbers, I could choose the number up to three times. However, the choosing is random, so I don’t really know how many times I will choose it.
Random list no repeats, manual and formulas solutionsFig. 1 A list of elements
If we want to choose an element from the list at random without repeats, we can use a manual solution. We need to write the RAND function which returns numbers from 0 to 1 with fifteen digit precision. It means that it’s almost impossible to choose the same number twice (Fig. 2).
=RAND()
Fig. 2 RAND function
After dragging formula down, we have 8 random numbers. We have to remember that the RAND function is a volatile function. It means that it is recalculated every time our worksheet changes (Fig. 3).
Fig. 3 Recalculation
Now, that we have our helper column, we can do the sorting. We go to Data tab and we choose the A to Z or Z to A command.
Fig. 4 Sorting
If we want to have, e.g. five elements, we have to choose five elements from the top. It doesn’t matter whether we sort from A to Z or Z to A. We just choose the first five elements. We can see that the numbers are recalculated every time we sort our data (Fig. 5)
Fig. 5 Five elements from the list
Let’s get back to our original list and try the second solution. It is from Legacy Excel and it uses formulas. Let’s start with a way to sort our numbers. We can use, e.g. the LARGE function, which returns the biggest number from the list, then the second biggest, then the third biggest, and so on. We have to press the F4 key to lock our list. Then, we need a way to choose the first, the second, and so on numbers. So, we want to change our key argument using a proper function. One of the simplest solution I know is using the ROW function with reference to cell A1, which is free (without $ signs). When we copy our formula down, our A1 reference will also go down, and the ROW function will return the first row, the second, the third, and so on (fig. 6)
=LARGE($B$2:$B$9,ROW(A1))
Fig. 6 ROW function
After entering the formula, we have our numbers placed from the largest to the smallest. Our five elements are now in a proper order. It means that now we can choose elements. However, we must use the MATCH and INDEX function in our data orientation. Let’s add the MATCH function to find the position of our number. We have to add the range, which is our random number column and press the F4 key to lock it. Then, we are adding 0 because we want to have the exact match (Fig. 7)
=MATCH(LARGE($B$2:$B$9,ROW(A1)),$B$2:$B$9,0)
Fig. 7 MATCH function
After entering and copying down, we have positions of the largest numbers. When we look at the first position in our Chosen column, we have 5. It means that the largest number from our original list is located in the 5th place.
Fig. 8 Position of the largest number
However, we don’t want to have position numbers, but the elements connected to those numbers. It means that we have to add the INDEX function to our MATCH function. This time, we want to choose elements from our list, so we are adding the range and lock it by pressing the F4 key. It turns out that our MATCH function is the row number. We have to close the formula (Fig. 9)
After entering and copying down, we have elements selected at random without repeats. When I press the F9 key, I am forcing it to recalculate our results. Sometimes, we see our number (123) twice because it is twice on our original list (Fig. 10)
Fig. 10 Elements selected at random
It’s high time we tried the final solution in Dynamic Array Excel. This solution is simpler that the one in Legacy Excel because we just need two functions. In DA Excel, we have the RANDARRAY function which can create a random array. We just need to copy the same column into our formula, which means that we need an array with eight rows and one column. That’s why we are writing just 8 (Fig. 11).
=RANDARRAY(8)
Fig. 11 RANDARRAY function
After putting our formula into Excel, we have eight random numbers. In DA Excel, we can sort by this column. Thus, we have to add the SORTBY function to our formula. The array which we want to sort is our list. This time, we don’t have to lock it because it is a DA formula, and our formula is the result (Fig. 12).
=SORTBY(A2:A9,RANDARRAY(8))
Fig. 12 SORTBY function
After putting the formula down, we have eight elements from our list in a random order. Pressing the F9 key allows us to choose elements once again at random. Let’s choose five elements from the top once again. We still can see the remaining elements. If you don’t want to see them, you have some options. You can, for example, select five elements from our list, then press the F2 key to go to the formula edit mode, then press the Ctrl + Shift + Enter combination to create an array formula. The array formula is from Legacy Excel. Now, we have only five elements because I’ve selected only five cells. Those cells are an array, which means that we cannot change or delete any single element of the array. If you want to change it, you have to change the whole array at one time (Fig. 13).
Fig. 13 An array
Summing up, if you want to choose an element at random from a list, you can use a manual solution, a Legacy Excel solution or a Dynamic Array solution.
