Here I am following a formula-based approach to merge values in two columns into one column in Google Docs Sheets.
If you use the Merge Cells menu command that available within the Format menu or on the toolbar in Google Sheets, you won’t be able to merge two columns into one.
The said menu command is for merging multiple cells into one cell in Google Sheets. Here is an example of merging cells using the said menu command in Google Sheets.
Cell A1 contains “Prashanth” and cell B1 contains “info inspired”. I want the text “Prashanth info inspired” after merging these two cells.
If I select A1:B1 and click Format > Merge cells > Merge all, it would pop up the message “Merging cells will only preserve the top-leftmost value. Merge anyway?“. So this is not the ideal way of merging two columns in Google Sheets.
I’m going to introduce to you two methods to properly merge two columns into one column.
- The first method is for general purposes like combine values in one column with values in another column called concatenation.
- The second method is unique the values in the first column and merge it with the second column.
The first method is generally used for combining first names in one column with the last names in another column. So we will get a single column with first and last names.
The second method is for merging a value column with a category column.
I’ll show you examples of both the types of merging of columns first. See the two tables below and concentrate on the third column that contains the expected results after merging.
TABLE # 1 (A1:C5)
| First Name | Last Name | Expected Result |
| Cheryl | Powell | Cheryl Powell |
| Irene | Sanders | Irene Sanders |
| Catherine | Watson | Catherine Watson |
| Roger | Allen | Roger Allen |
TABLE # 2 (A1:C15)
| Weekdays | Sales | Expected Result |
| Monday | 100 | Monday |
| Monday | 125 | 100 |
| Monday | 130 | 125 |
| Tuesday | 50 | 130 |
| Tuesday | 55 | Tuesday |
| Wednesday | 100 | 50 |
| Thursday | 120 | 55 |
| Thursday | 130 | Wednesday |
| Friday | 140 | 100 |
| Thursday | ||
| 120 | ||
| 130 | ||
| Friday | ||
| 140 |
If you are looking for these two types of merging of columns in Google Sheets, you can proceed further.
Here are the formulas to combine two columns into one column as above in Google Sheets.
Combine the First Name with the Last Name Using an Array Formula
Copy values in the first two columns, i.e. in A1:B5 in TABLE # 1 above, and paste it into your sheet in the same range.
To combine the first name in a cell with the last name in another cell, I mean to combine two adjoining or distant cells, we can use any of the below formulas.
I am merging the first name in cell A2 (Cheryl) with the last name in cell B2 (Powell). Here are the formulas.
FORMULA # 1
=A2&" "&B2The above formula in cell C2 will concatenate the first and last names and insert a space delimiter in between the names.
Result: Cheryl Powell
Right-click on cell C2 and click “Copy”. Again right-click and this time click Paste special > Paste values only. This will convert the formula to value.
Other Formulas:
FORMULA # 2
=concatenate(A2," ",B2)FORMULA # 3
=concat(A2&" ",B2)Two more formulas using the join functions.
FORMULA # 4
=JOIN(" ",A2,B2)FORMULA # 5
=TEXTJOIN(" ",true,A2,B2)Out of the 5 formulas, only the formulas 1 and 3 supports the ArrayFormula function to expand to a whole selection.
I mean to merge first names with the last names in two columns into one column, either you should use any of the formulas 2, 4, or 5 in cell C2 and copy-paste down or;
simply use the array formula versions of formula # 1;
=ArrayFormula(A2:A5&" "&B2:B5)or formula # 3 in cell C2.
=ArrayFormula(concat(A2:A5&" ",B2:B5))
Select C2:C5 (Ctrl+C) and copy the values. Then paste it as values (Ctrl+Shift+V) in the same range.
Unique the First Column and Merge It with the Second Column
This type of merging of two columns is useful when you have a category column and a value column to combine.
Please see TABLE # 2 above. It’s some kind of pivoting a data set without using any aggregation function.
Let’s learn this advanced level of merging of two columns in Google Sheets. There are 3 steps in this step-by-step approach of concatenating two columns into one column.
Before begin, copy the values in the first two columns in TABLE # 2, i.e. the range A1:B10, into your sheet A1:B10.
If you are using your own dataset, please make sure that the table is sorted based on column A.
Regarding the table, weekdays in column A are the category and the sales value in column B to be merged with this category after removing duplicate weekdays. That’s is the task.
Actually what we are going to do is first we will make the weekdays (category) unique. Then we can insert the corresponding values from the next column below each category.
Here we can use none of the formulas used to merge the first name with the last name in our first example. See the steps below.
Step 1 – Running Count of Each Category
In cell C2 insert the following formula to return the running count of the weekdays.
STEP 1 FORMULA
=ARRAYFORMULA(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)))
I have used A2:A instead of A2:A10 to cover future entry.
For formula explanation, you can read my post – Running Count in Google Sheets – Formula Examples. On that page, scroll down to the subtitle Cumulative Count of All the Items in a Sorted List.
Step 2 – Replace Duplicate Weekdays with Blank
We can use the step 1 formula in the following syntax, within an IF, to replace duplicate weekdays with blank.
Syntax: ArrayFormula(if(step_1_formula=1,A2:A,))
STEP 2 FORMULA
=ArrayFormula(if(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))=1,A2:A22,))
Step 3 – Merge Column B with the Step 2 Column – Two Columns into One
Using the FLATTEN function (a ‘hidden’ function within sheets), we can now merge the step 2 formula output with column B values.
Flattening won’t alone be enough as it will include several blank rows and may freeze your sheet due to the infinite (open) range in the formula.
Here is the flatten formula syntax and the formula. Please don’t insert it in your sheet!
Syntax: flatten({step_2_formula,B2:B})
STEP 3 FORMULA_1
flatten({if(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))=1,A2:A22,),B2:B})We can use the FILTER function to filter out the blanks. So the formula will work without any issue in sheets.
Syntax: filter(step_3_formula_1,(step_3_formula_1<>"")
FINAL FORMULA
=filter(flatten({if(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))=1,A2:A22,),B2:B}),flatten({if(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))=1,A2:A22,),B2:B})<>"")
Resources
Is there a way to put spaces between each day of the week?
Hi, Patrick,
See if this tutorial helps?
How to Automatically Insert a Blank Row below Each Group in Google Sheets.
Hi, Jordan!
Is it possible to apply the second method in Excel?
Hi Prashanth,
Is it possible to do the same as the above but insert the new group at intervals of 10 (assuming that there are always less than 8 values per day)?
i.e Monday and its values starting from C1, Tuesdays and it’s values starting from C11, Wednesday and it’s values starting from C21, etc.
So it won’t matter how many values are assigned to each day, the new group is started at the next interval.
Hi, Jordan,
I didn’t understand your question 🙁
Apologies, I have created an example sheet with a better explanation:
(URL has been removed by the Admin)
I’ve also added an extra request on the sheet to add a standard string between column A and column B when combining them.
Hi, Jodan,
Sorry! I don’t find a way to modify the current formula. But I do have some ideas. Here is it.
In the “New Example” tab, empty the columns D1:Z1.
Insert the below unstack formula in cell E1.
=ArrayFormula(transpose(trim(sort(split(transpose(query({transpose(unique((A2:A)));IF(len(B2:B),IF(transpose(
unique(A2:A))=A2:A,"-"&B2:B,""),"")},,1000)),"-")))))
The above formula is from my Unstack Data tutorial.
Then use the following Flatten in cell L1 to merge the two columns.
=flatten(transpose(F1:J10))Note: This is an untested formula. It has issues like sorting Weekdays in A-Z order.