join-multiple-columns-into-1-sorted-column.md

Further to my previous post on combining 2 columns into one column I've had to do some further work on this by now combining a number of unknown columns into one unique column and then sorting the result.

I've been able to achieve this and will demonstrate one way in how I've been able to get it to work. Let's use a working example to show how it works.

First, we'll acquaint ourselves with the old method of using this formula, which in this case would be the following for the following three columns (column A will be our results column):

=unique(transpose(split(join(";",A:A)&join(";",B:B)&join(";",C:C),";")))

 Here is the data set we will be working with

Unfortunately the problem with this method is that is requires us to enter in the join(delimiter,range) function for each new column, and I want the function to be able to concatenate all cells in a range of columns.

Our first remedy at finding a solution avails itself when using the ArrayFormula function. As this function will loop through each of the cells in your array (being a range of cells) and will output each cell according to the way you define the function inside.

As an example, if we were to apply the following formula in cell A1 we would get the following:

=arrayformula(concatenate(if(len(B:D)>0,B:D&";","")))

 Use ArrayFormula to concatenate results into a string

As you can see what happens in the above function is that the ArrayFormula goes through my range B:D (being everything in columns B to D) and once it finds a cell that contains something (a string length greater than 0) it then gets that result and appends a semi-colon to it - this is denoted with the true statement in the if statement by B:D&";".

Now we simply merge the two formulas together, in essence replacing the join section of our previous formula with the ArrayFormula above, producing:

=unique(transpose(split(arrayformula(concatenate(if(len(B:D)>0,B:D&";",""))),";")))

 Merge multiple columns into 1 column

Lastly, if we wanted to sort the result of this output we would finally wrap the resulting formula in the sort function, where we would have everything sorted from A to Z:

=sort(unique(transpose(split(arrayformula(concatenate(if(len(B:D)>0,B:D&";",""))),";"))))

 Join multiple columns together and sort results

Of course if I didn't know how many columns I would be expecting in this sheet I would amend the last column name to a much large letter, eg.

=sort(unique(transpose(split(arrayformula(concatenate(if(len(B:Z)>0,B:Z&";",""))),";"))))

 Extend more columns into the join as necessary

However, do be aware processing time will increase with the more data columns you add.

What If I Just Want To Merge Two Columns Into One?

If you want to explore another method of merging two columns into one, that uses a similar technique to the one raised above, but using a couple of different functions, then check the article on merging two columns into one on a spreadsheet.