How To Merge Duplicates In Google Sheets

How to merge duplicates in Google Sheets
Reading Time: 6 minutes
Formulae
=UNIQUE(A2:A) //To return all unique values in column A

=JOIN(“, “, FILTER( B: B, A: A=E2)) //Filter based on E2 value and join the column

=QUERY(A2:B7, "select A, Sum(B) where A is not null group by A")

Sample Google Sheets template with formula here.

Google Sheets is one of the most popular data management tools businesses use, allowing users to create and store spreadsheets. Documents that contain data are organized into rows and columns. But there could be repeated information in different lines of one column, so this would require merging the column to avoid clustering. There are several ways to merge duplicates in Google Sheets to make sorting out data easier.

Why Merge Duplicate In Google Sheets?

Merging duplicate rows in Google Sheets can help you reduce the clutter in your data, improve the readability of your spreadsheet, and make it more efficient. You can use the same method to merge duplicate rows in Google Sheets.

For example, if you have a list of clients in your spreadsheet and some of them have the same email address, you can merge those rows into one. This action will reduce the clutter and make your data easier to read. In addition, it makes it easier to find specific information because you only have one row for each client instead of two.

2 Simple Ways to Merge Duplicates in Google Sheets

You can merge duplicate rows in Google Sheets using the following two methods:

  • Using the UNIQUE + JOIN function
  • Using the QUERY Function

Merge Duplicates In Google Sheet Using UNIQUE + JOIN Function

The UNIQUE function is used to find the unique values of a column. The JOIN function combines or appends two or more columns based on a common value (or condition). Using this method, you can merge duplicate rows in Google Sheets by merging the data with the UNIQUE + JOIN functions.

So If you have a table like the one below, this function can find the unique values in column A and merge the ones in columns B and C.

Sample data to merge duplicates in Google Sheets

Step 1: Create a new table with the same headers by the right corner of the initial table.

Create a new table to place the merged duplicates

Step 2: Enter the UNIQUE function to check for the same fruit in column A. The formula for the unique function is

=UNIQUE(A2: A)

A2: A is the column range that we want to merge the duplicates.

Formula of the UNIQUE function

Step 3: Press Enter. 

The fruits that repeatedly appear in the first table have been merged by the UNIQUE function as shown below.

The uniques returned by the UNIQUE function

Step 4: Enter the JOIN function below in the cell beneath the Quantity header.

=JOIN(", ", FILTER( B: B, A: A=E3))
The JOIN and FILTER formula to merge duplicates

The FILTER function searches column A for all instances of the value in cell E2 and pulls corresponding records from Column B. JOIN combines these values into one cell and separates them with a comma.

Drag down the formula cell to replicate and apply the formula to the rest of the cells in the column.

Step 4: Press Enter to return the result. 

The JOIN function has pulled out the corresponding values from the E column. Drag the result to all down to the last cell to apply the formula to all the cells.

Here is the result below.

Result of the JOIN and FILTER function

Step 5: Enter the JOIN function for column C to merge the labels. This step uses the same function as the previous Column. 

The formula used is as given below.

=JOIN(", ", FILTER( C:C, A:A=E3))
Applying JOIN and FILTER function to the "Label" column

Step 6: Press Enter.

Joined labels

The UNIQUE + JOIN function has merged all the values in each column. 

NOTE: You cannot use the JOIN function on two columns simultaneously. We have used this function for each column, one after the other.

Merge Duplicates In Google Sheets Using Query Function

Using the QUERY function, you can also merge duplicates in Google Sheets. And this will allow you to merge duplicates based on cell values. To merge duplicate cells using the QUERY function, you need to have a formula that finds the duplicates and then merges them. 

Syntax Of Query Function In Google Sheets

The syntax for this query is as follows

 =QUERY ( data, query,[headers])
  • data: represents the range of cells that we want to find duplicates.
  • query: represents the formula that will be used to find the duplicates.
  • headers: this is an optional argument that allows you to specify which column contains headers in your data

Let us consider again the previous example again and merge duplicates in Google Sheets using the QUERY function.

Sample data to merge duplicates in Google Sheets using the QUERY function

Here are the steps on how to use the QUERY function to merge duplicates in Google Sheets:

Step 1: Create a new table with the same row headers as the first.

New table to apply the QUERY function

Step 2: Select the cell in the Fruit header and enter the Query formula shown below.

=QUERY(A3:B9, "select A, Sum(B) where A is not null group by A")
QUERY function in Google Sheets

The data ‘A3:B9’ is the range of the cells we want to merge. The query gives the command which sums the values in column B grouped according to A.

Step 3: Press Enter. 

So after this formula, here is the result of merging the data.

Duplicates merged using the QUERY function

Merge duplicates in Google Sheets using pivot table

Pivot table is one of the useful features that can be used to achieve various and diverse ends. Combining and summing values in one of them. We can use the pivot table to merge duplicate values and sum their values.

Follow the steps below to merge duplicates using pivot table.

Select the range of cells including the headers, in our case, A1:B8.

Click Insert and select Pivot table.

Adding pivot table in Google Sheets

Select Existing sheet to insert the table (or New sheet, if you want to insert it in a new sheet) and select the cell to place the table. Then click Create.

Creating a pivot table

Add the row values from the Pivot table editor. Add Fruit as the value.

Adding pivot row

Add Quantity as the values.

Adding pivot row values

The duplicates will be merged and their sum will be added in a separate column.

Values merged using pivot table

Conclusion

If you are looking for a simple way to merge duplicates in Google Sheets, then you can use the Query function or the UNIQUE + JOIN as they are easy methods. With these functions, you can merge and combine values repeatedly appearing in a column.

See also

You can check out related topics below

How To Use The Query Function In Google Sheet

SUMPRODUCT Function In Google Sheet

How To Count Unique Values In Google Sheets