Google sheets merge rows with same value

Take the question of how to merge duplicate rows in Google Sheets in a broader sense. You can merge multiple rows into one in different ways. I have three methods and each method will produce different outputs.

Merging duplicate rows depends on the duplicates in the primary column (the column to unique) and also in the other columns in the table.

To understand this, you should first know the difference between merging duplicate rows and removing duplicate rows.

In both merging duplicate rows and removing duplicate rows the action the concerned formula performs is converting multiple duplicate rows into one. But the output will be, of course, different.

Let me start with removing duplicates. So that I can easily make you understand different aspects related to merging of duplicate rows.

Removing Duplicate Data Using Unique and Sortn Formulas

For example, if row # 4 has a duplicate in row # 5, when removing duplicates the row # 5 will be removed. This you can achieve with the function Unique.

As an example consider the data in A1:G7 below. Using the Unique function I have removed the 5th row.

The formula =unique(A2:G) is in cell I2 and the result in I2:O5. That is what the Unique does.

Now take a look at the result in I8:O9 returned by the Sortn formula =sortn(A2:G,9^9,2,1,0) in I8. What is the difference between the Unique and Sortn formula output above?

The Unique formula considers the entire row (all columns in a row) to determine the duplicate whereas the Sortn only considers the first column in the row.

Of course, in Sortn you can include second, third or all columns. Want to know that? Follow this – How to Apply Unique in Selected Columns in Google Sheets. Actually, this is something that you can achieve via the new Remove Duplicates menu command.

Now let’s concentrate on our main topic that is how to merge duplicate rows and concatenate/join values.

How to Merge Multiple Duplicate Rows into One in Google Sheets

Take a look at the Sortn formula output above. It uniques the first column but not merging the data from duplicate rows based on this unique column.

Here are the three different types of merging of duplicate data in Google Docs Sheets that I am going to explain.

1. Merge duplicate rows.
   1. Concatenate values in the same column.
   2. Concatenate unique values in the same column.
2. Merge duplicate rows and skip values in the same column.

In this, in point # 1.1 and in point # 1.2, I have used non-array formulas. That means the formulas are in cell J2 and J6 respectively which copied to the cells horizontally and vertically.

Point # 3 uses a single Query formula in cell I10 which I have already detailed here – Formula to combine duplicate rows. So I am skipping that in this tutorial.

Merge Duplicate Rows and Concatenate Values in Google Sheets

Here we are merging duplicate rows based on the unique column A. Column A has the unique names “Steve” and “John”.

The multiple rows containing these names merged into one (one row for “Steve” and another row for “John”). While doing so, the values in other columns are concatenated. I hope the below image can give you a better idea about this.

Find the formula and the step-b-step instructions below.

Steps:

1. Copy the header row A1:G1 to I1:O1.
2. Enter the unique names in I2:I3 or use the Unique formula =unique(A2:A) in I2.
3. Enter this formula =textjoin(", ",true,filter(B$2:B$14,$A$2:$A$14=$I2)) in cell J2 and copy to the range K2:O2 and J3:O3.

As you can see I am using the Textjoin and Filter functions to merge the duplicate rows in Google Sheets and concatenate values in columns.

Filter Function to Filter Values in Duplicate Rows

In the above formula take out the Filter formula. What does it do?

=filter(B$2:B$14,$A$2:$A$14=$I2)

B$2:B$14 – filter range.
$A$2:$A$14=$I2 – a condition.

This formula filters column B2:B14 if values in A2:A14 is I2. That means filters the values in column B if the name in column A is ‘Steve’.

I have copied the formula in J2 to J3 and to other cells (J2:O3). Here is the copied formula in J3.

=textjoin(", ",true,filter(B$2:B$14,$A$2:$A$14=$I3))

The only change in the Filter formula is the condition/criterion reference. It changed from I2 to I3.

The range and criteria range are constant as I’ve used absolute cell references. See the use of Dollar signs in the formula which determines absolute and relative cell references.

I am taking out the Filter part outside and enter it in cell J6. See that below.

Now we can see the role of Textjoin function in this.

Textjoin Function to Concatenate Values in Duplicate Rows and Insert a Delimeter

The Textjoin function concatenates the filtered values. You can also use the Join function here. I have used the Texjoin as it is slightly better in concatenating values.

=textjoin(", ",true,

In Textjoin, the ", " is the delimiter (the separator placed between the joined values) and true ignores the empty cell.

Merge Duplicate Rows and Join Unique Values in Google Sheets

When you merge data in duplicate cells sometimes you may want to only concatenate unique values.

For example, take a look at cell K3 in the above example. There the number 10 repeats twice. You can unique that using the Unique with Filter.

Compare to the above formula, here you should add the Unique function as below.

=textjoin(", ",true,unique(filter(B$2:B$14,$A$2:$A$14=$I2)))

Since the Filter and Textjoin play the same role here, I am not detailing that part again. Refer to the below Sheet for the formulas. You can copy the Sheet and experiment with the formulas.

Example Sheet 16719

Hope you have found this Google Sheets Data merging tips useful. Enjoy!