Merge data from duplicate rows in Google Sheets based on a unique column

CONCATENATE – Google Sheets function and operator to join records

The first thing that comes to mind for merging duplicate rows is Google Sheets CONCATENATE function. Or, you can use the ampersand (&), a simpler concatenation operator. Here's how it works.

Suppose you have a list of movies you'd like to organize by genre:

• You can merge cells in Google Sheets using spaces between the values:

  =CONCATENATE(B2," ",C2," ",B8," ",C8)

  =B2&" "&C2&" "&B8&" "&C8

• Or use any other symbols to combine duplicate rows:

  =CONCATENATE(A3,": ",B3," (",C3,"), ",B6," (",C6,") ")

  =A3&": "&B3&" ("&C3&"), "&B6&" ("&C6&") "

Once the rows are merged, you can get rid of formulas and keep only the text as I showed in this tutorial: Convert formulas to values in Google Sheets

As simple as this way may seem, it is obviously far from ideal. It requires for you to know the exact positions of duplicates, and it's you who should point them out to the formula. So this is ideal for smaller datasets but what do you do with bigger ones?

Merge cells while keeping data with UNIQUE + JOIN

This formula combination finds duplicates in Google Sheets and merges cells with unique records for you. However, you are still in charge and have to show the formulas where to look. Let's see it in action.

1. I start by using Google Sheets UNIQUE to filter duplicate genres in column A:

   =UNIQUE(A2:A)

   The formula returns a list of all genres even if they appear multiple times in the original list. In other words, it removes duplicates from column A.

   Tip. UNIQUE is case-sensitive, so make sure to bring the same records to the same text case. This tutorial will help you do that quickly in bulk.

2. My next formula will use JOIN & FILTER to merge titles from column B for each unique genre from column E:

   =JOIN(", ",FILTER(B:B,A:A=E2))

   So how exactly does this formula work?

   • FILTER scans column A for all instances of the genre from column E. Once located, it pulls corresponding records from column B.
   • JOIN unites these values in one cell with a comma.

   Copy the formula down and you'll get all the titles sorted by genre.

   Note. In case you need years as well, you'll have to create the formula in the neighboring column since JOIN works with one column at a time:

   =JOIN(", ",FILTER(C:C,A:A=E2))

QUERY function to remove duplicate lines in Google Sheets

The QUERY function is a more advanced, flexible solution for larger datasets. It may seem a bit tricky at first, but once you learn how to use it, it will become your true companion in spreadsheets. Here’s its basic syntax:

• where data is a range of your source table.
• and query is a set of commands to dictate conditions to get specific data.

  Tip. You can get a full list of all commands here.

• headers is an optional argument. It's the number of header rows in your source table.

To put it simply, Google Sheets QUERY returns some sets of values based on the conditions you specify.

1. Start with your dataset

First, check that duplicate entries you want to merge are organized in the same columns. For example, a list of authors with repeated names but different books, as in my example.

The add-on will automatically detect your entire data range on the active sheet:

But you can easily switch to another sheet, select or enter a different range, and follow the next step where you specify the key column with duplicates.

2. Select key columns with duplicate records

Choose the columns containing the repeated data. In my case, they are First Name and Last Name:

If your dataset includes multiple types of duplicate information, such as Company Name, City, and Country, just select them all — the add-on will handle them all.

3. Choose columns to merge

Now for the fun part: decide how you want your duplicates combined.

For text values, you can specify delimiters to separate them, like commas, semicolons, or even line breaks.

For numeric data, you have the option to merge or apply functions such as SUM, AVERAGE, or COUNT to calculate totals.

In my case, I'd like to merge all books written by the same author into one cell and separate them by line breaks, making the data more readable. As for the quantity, I'm okay with totaling all books per author. The numbers for duplicate titles, if there are any, will be added together.

4. See the result

Once you hit the Combine button, the add-on will immediately merge the rows. Within seconds, you’ll see a confirmation message:

Along with a merged dataset where all duplicates are combined into one. Here's a part of the result I've got:

Video: How to combine duplicate rows in Google Sheets

Whenever you want to group duplicate rows in your spreadsheet, remember there's a simple and quick way to merge and sum up all related values. Since a picture is worth a thousand words, here's a short video that will show everything you need to know about the add-on:

If you don't have Combine Duplicate Rows yet, you will find it either as part of the Remove Duplicates add-on for Google Sheets:

or as part of Power Tools with 40+ other daily solutions for Google Sheets:

Use scenarios to automate merging duplicates

If you find yourself regularly combining duplicate rows using the same settings, save them into reusable scenarios. Scenarios let you rerun the same merge options with a single click whenever needed, even for different datasets.

You simply save your scenario with a name, range, and sheet — perfect for recurring tasks:

The settings you save here can be quickly called for from the Google Sheets menu. The add-on will start combining duplicate rows right away, sparing you some extra time:

I encourage you to get to know the tool and its options better, for Google Sheets is dark and full of terrors if you know what I mean ;)