Concatenate function for sorting

As I said before, the concatenate function can be used in a great many useful ways, and one of those ways is to make it easier to sort.

There are two things which often annoy me when I’m doing frequent sorts. The first is building the sort, and the second is dealing with the limitation on the number of columns that can be used in the sort. It was the latter problem that made me consider the value of concatenation for sorting.

Then I saw how making a column of concatenated values could mean the end to rebuilding sorts. One annoying thing about sorting is that most spreadsheet programs can’t be relied on to remember them, especially if they’re complicated or you’ve sorted something else in the meantime. After the third or fourth repetition of “sort this column first, then the other, then this third one…” I generally get heartily sick of it. So if I’m going to be repeatedly sorting — especially if I might have multiple different ways I might want to sort — I make one “sort” column for each sort I frequently do.

To make a sort column, I just use concatenate, and for parameters I use the columns I want to sort. Say, for example, in my book inventory, I might have =concatenate(author,series,title). This would sort with author as the primary field, followed by series and the title. I could also have another sort column with =concatenate(publisher,author,series,title) if I wanted, if there’s a publisher that’s prone to having sales. That turns a multi-column sort into a single-column sort, which is much simpler.

Once I’ve set up the formula, usually in the second row (the first row being reserved for column names), I copy it, then navigate down to the bottom row in the list, then highlight everything up to the second row (which is the next populated cell, when you’re heading up from the bottom), and paste the formula. Unlike when I am initially creating the list I don’t usually copy the formula beyond the end of the list, because doing so would likely sort all the blank rows to the top.

An example is available as Project 6: Concatenated Sorts.