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 data in 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 and making the data easier to read and thus more useful.
- 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 enabling you to read the data quickly. 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 combine 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.
Three simple ways to merge duplicates in Google Sheets
You can combine duplicate rows in Google Sheets using the following two methods:
Merge duplicates in Google Sheets using UNIQUE + JOIN functions
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 to 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 as given under.
=UNIQUE(A2: A)A2: A is the column range that we want to merge the duplicates. The UNIQUE function will combine the duplicates and return all the unique values in the column.
Step 3: Press Enter to return all the unique values while excluding repeating values.
As we can see from the image below, the fruits that appear more than once in the first table have been merged 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 FILTER function searches column A for all instances of the value in cell E2 (in this case, Lime) and pulls corresponding records from Column B (“Quantity”). 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 except for this one we combine the values in column C (“Label”).
The formula used is as given below.
=JOIN(", ", FILTER( C:C, A:A=E3))
Step 6: Press Enter to combine the values.
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 the 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 cell range within which we want to find duplicates and combine them. The query gives the command which sums the values in column B grouped them according to the value in column A.
Step 3: Press Enter to combine the duplicates.
Below is the result of the merged 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 pivot table to combine duplicate values in Google Sheets and return the sum of the individual values.
Follow the steps below to merge duplicates using pivot table.
1. Select the range of cells including the headers that you want to merge, in our case, A1:B8.
2. Click Insert and select Pivot table.
3. 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.
4. Add the row values from the Pivot table editor. Add “Fruit”, ie the header whose values you want to merged, as the value.
5. Add “Quantity” as the values–the column that you want to get the sum for.
5. The duplicates will be merged and their sum will be added in a separate column as can be seen from the image below.
Conclusion
If you are looking for a simple way to merge duplicates in Google Sheets, then you can use the pivot table or the UNIQUE + JOIN as they are easy methods. For a more complex operations, you can use the QUERY function–which is one of the most versatile functions in Google Sheets and can accomplish various other things besides merging duplicates. 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