Merging duplicate rows in Google Sheets can be a complex task that involves merging cell values, aggregating numbers, and concatenating text, dates, or timestamps. Combining rows with identical information requires careful consideration to avoid data loss. If you’re looking for an array formula-based approach without using any add-ons, I have a solution that may be perfect for you.
The Challenge of Merging Duplicate Rows in Google Sheets
Unfortunately, there is no built-in function or menu command that allows you to merge duplicate rows in Google Sheets in the desired way. However, you can solve this problem by using three array formulas, each assigned to a specific task:
- The first array formula will remove duplicate rows by merging the cells in the specified columns.
- The second array formula will act as an alternative to the CONCATENATE function or JOIN function, joining the values from the removed rows with the existing rows.
- The third array formula will conditionally sum columns, adding the removed rows’ values to the existing rows.
With these array formulas, you can handle most tasks related to merging duplicate rows. While the UNIQUE function can merge cells, it falls short when it comes to aggregating and concatenating values.
How to Merge Duplicate Rows in Google Sheets: An Example
Let’s walk through an example to illustrate how to merge duplicate rows in Google Sheets. In this example, our source data for merging duplicate rows is in the cell range A2:E, and the results will be displayed in the cell range G2:K.
Figure 1: Merging Duplicate Rows: Single Column Unique
First, we will remove duplicates in the “Item” column (A), resulting in unique fruit names in column G. Then, we will sum the corresponding quantities in column B and the amounts in column D separately, with the results displayed in columns H and J. We will also concatenate the rates in column C for each fruit in column I, as well as concatenate the grade in column E for each fruit in column K.
To achieve this, we will use three array formulas, one for each task. Additionally, these formulas can be adjusted to customize the formula to your dataset.
Keep in mind that these formulas can be used with regular spreadsheet data, Google Form data in Sheets, as well as IMPORTRANGE data.
If you want to learn more about the array formulas for merging duplicate rows in Google Sheets and how to use them, check out this tutorial.
Step 1: Unique to Merge Duplicate Cells
Let’s start with the first step: merging duplicate cells. In this step, we will use the UNIQUE function to unique the values in the desired column (column A in our example) and remove any blank cells.
The following formula will achieve this:
=LET(key,A2:A,UNIQUE(FILTER(key,CHOOSECOLS(key,1)<>"")))Simply replace A2:A with the corresponding column range in your sheet.
Step 2: Aggregating Values while Retaining Removed Rows’ Values
Merging rows involves not only removing duplicate rows but also retaining the values in those rows. Depending on the data type, such as text, date, time, or timestamp, we may need to combine or sum the values.
To sum the values corresponding to the merged values from Step 1, we can use the SUMIF function. In our example, the sum_range would be B2:B, the range would be A2:A, and the criterion would be the unique items in G2:G.
=ARRAYFORMULA(LET(range,A2:A,sum_range,B2:B,SUMIF(TRANSPOSE(QUERY(TRANSPOSE(range),,9^9)),TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(range,CHOOSECOLS(range,1)<>""))),,9^9)),sum_range)))You can use the same formula in cell J2 to sum the Amount column by replacing sum_range B2:B with D2:D.
Step 3: Concatenating Values while Retaining Removed Rows’ Values
In the final step, we will concatenate the rates in the removed rows with the existing rates. This process involves two columns: the column used to identify the unique rows from Step 1 and the rate column.
Here is the formula for column I:
=LET(merge,A2:A,combine,C2:C,MAP(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(merge,CHOOSECOLS(merge,1)<>""))),,9^9)),LAMBDA(row,TEXTJOIN(", ",TRUE,FILTER(combine,TRANSPOSE(QUERY(TRANSPOSE(merge),,9^9))=row)))))To concatenate the Grade column, you can use the same formula but replace the range C2:C with E2:E.
Now you have the key formulas to merge duplicate rows and concatenate values in Google Sheets. This three-step process can be applied to different datasets with multiple columns you want to merge.
Merging Duplicate Rows and Multiple Columns for Unique in Google Sheets
In some cases, you may want to merge duplicate rows based on multiple columns. Let’s consider a new sample dataset:
Figure 5: Merging Duplicate Rows: Two-Column Unique
In this example, we want to merge rows based on the first and last names in columns A and B, respectively. The “Mark” should be aggregated, and the “Subject” should be concatenated.
To achieve this, follow these step-by-step instructions:
- Scroll up and copy the formula under “Unique to Merge Duplicate Cells.” Replace the range A2:A with A2:B and insert the formula in cell F2.
- Similarly, copy the first formula under “Aggregating Values while Retaining Removed Rows’ Values.” Replace A2:A with A2:B and B2:B with C2:C.
- Then, copy the first formula under “Concatenating Values while Retaining Removed Rows’ Values.” Replace A2:A with A2:B and C2:C with D2:D.
That’s it! You can now use these formulas to merge duplicate rows in Google Sheets.
