How To Merge Two Columns Into One (Spreadsheet)


Recently, I had to merge two columns into only one column on a spreadsheet.

The way I found to do this was by using the following common spreadsheet functions: JOIN, TRANSPOSE and SPLIT, and if needed UNIQUE.

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:

Appending two columns of data, our example

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:

Appending two columns together into one

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:

Appending one column onto another

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:

Merging two columns together and returning only unique values

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!

What If I Want To Merge Multiple Columns Into One?

If you want to merge multiple spreadsheet columns into one, then you will to check our article on merging multiple columns into one (rather than just two as detailed below).

Ryan

Author of scripteverything.com, Ryan has been dabbling in code since the late '90s when he cut his teeth by exploring VBA in Excel when trying to do something more. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP. When he is not behind a screen, Ryan enjoys a good bush walk with the family during the cooler months, and going with them to the beach during the warmer months.

Recent Posts