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 duplicates
- Merge duplicates using UNIQUE+JOIN function
- Merge duplicates using QUERY function
- Merge duplicates using pivot table
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.
Step 1: Create a new table with the same headers by the right corner of the initial table.
Step 2: Enter the UNIQUE function to check for the same fruit in column A. The formula for the unique function is
A2: A is the column range that we want to merge the duplicates.
Step 3: Press Enter.
The fruits that repeatedly appear in the first table have been merged by the UNIQUE function as shown below.
Step 4: Enter the JOIN function below in the cell beneath the Quantity header.
=JOIN(", ", FILTER( B: B, A: A=E3))
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.
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))
Step 6: Press Enter.
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.
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.
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")
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.
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.
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.
Add the row values from the Pivot table editor. Add Fruit as the value.
Add Quantity as the values.
The duplicates will be merged and their sum will be added in a separate column.
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.
You can check out related topics below