UPDATE (4 Nov 13): I’ve created another post that allows you to perform the same task demonstrated below, but works better with multiple columns (rather than just two as detailed below).
There are times within a Google Spreadsheet (or I guess any spreadsheet for that matter) where you need to merge two columns together to form one column. It comes in really handy when you are looking to use a series of cells for data validation and both bits of data span over two separate columns.
Just how can we get them into one column?
I had a case recently where I needed to append the contents of one column underneath the contents of another column, with the size of both column’s height unknown (this eliminated the easier approach of simply placing the contents underneath manually).
I was able to find a solution, and I’ll illustrate how it worked by using an example. Let’s assume the following columns of data:
The first thing we will do is move to the next adjacent column, column C, and in cell C1 enter the following formula:
=join(";",A:A)&join(";",B:B)What this formula does is mesh all the data in the first column into a single string where each cell’s content is appended together with a semi-colon. Now if your data contains semi-colons you will want to use another symbol that is not used in your data set.
Our output in cell C1 would look a little something like this:
Lions;Tigers;Elephants;Lemurs;ABC;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;Red;Yellow;Green;Blue;White;Black;ABC;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;Now we’re going to amend our code in C1 so that we can split each item back into their own cell, our code in cell C1 will now look like so:
=split(join(";",A:A)&join(";",B:B),";")This would nor produce something a little like this:
Then as we need the data to be placed into a column we would transpose our data, by amending our cell in C1 to this:
=transpose(split(join(";",A:A)&join(";",B:B),";"))Giving us:
Now if we want to remove any pieces of data that are the same within both columns we would just further wrap the UNIQUE function around the TRANSPOSE function. In our working example this would remove the second ABC in our new column. Here’s the formula firstly:
=unique(transpose(split(join(";",A:A)&join(";",B:B),";")))Resulting in:
This has certainly helped me when operating with ImportRange function calls and then working with the imported data in the active sheet. Hopefully it helps you too!
