Combine data from multiple rows into one row based on column value in Google Sheets

Merging duplicate rows in your spreadsheets may turn into one of the most intricate tasks. Let's see how Google Sheets formulas can help and get to know one smart add-on that does all the job for you.

• Google Sheets functions to combine rows with the same value
  • CONCATENATE — Google Sheets function and operator to join records
  • Merge cells while keeping data with UNIQUE JOIN
  • QUERY function to remove duplicate lines in Google Sheets
• Fastest way to combine duplicate rows
  • Video: How to combine duplicate rows in Google Sheets
  • Use scenarios to automate merging duplicates

Google Sheets functions to combine rows with the same value

You didn't think Google Sheets would lack functions for this kind of task, did you? ;) Here are the formulas you will need to consolidate rows and remove duplicate cells in spreadsheets.

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:

=QUERY(data, query, [headers])

• 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.

Example 1

I want to get only comic book movies:

=QUERY(A1:C,"select * where A='Comic Book'")

The formula processes my entire source table (A1:C) and returns all columns (select *) for comic book movies (where A='Comic Book').

Tip. I don't specify the last row of my table (A1:C) intentionally — to keep the formula flexible and return new records in case other rows are added to the table.

As you can see, it works similar to a filter. But on practice, your data can be much bigger — with numbers you may need to calculate.

Example 2

Suppose I'm doing a little research and keeping track of the weekend box office for the newest movies in theaters:

I use Google Sheets QUERY to remove duplicates and count the total earned per movie for all weekends. I also organize them by genre:

=QUERY(B1:D, "select B,C, SUM(D) group by B,C")

Note. For the group by command, you must list all columns after select, otherwise, the formula won't work.

To sort records by movie instead, simply change the order of columns for the group by:

=QUERY(B1:D, "select B,C, SUM(D) group by C,B")

Example 3

Let's assume you successfully run a bookstore and keep track of all books in stock in all your branches. The list goes up to hundreds of books:

• Due to the hype over the Harry Potter series, you decide to check how many books you have left written by J.K.Rowling:

  =QUERY(Books!A1:D,"select A,B,C,D where A='Rowling'")

  

• You decide to go further and keep only the Harry Potter series omitting other tales:

  =QUERY(Books!A1:D,"select A,B,C,D where (A='Rowling' and C contains 'Harry Potter')")

  

• Using the Google Sheets QUERY function, you can also count all these books:

  =QUERY(Books!A1:D,"select A,B, sum(D) where (A='Rowling' and C contains 'Harry Potter') group by A,B")

  

I guess for now you've got an idea of how Google Sheet QUERY "combines duplicates" in Google Sheets. Though it's an available-to-all option, for me, it's more like a roundabout way of combining duplicate rows.

What's more, until you learn the commands it uses and the rules of applying them, the function won't be much of a help.

Fastest way to combine duplicate rows

If you're tired of manually combining rows with duplicate values, our add-on, Combine Duplicate Rows for Google Sheets, will be your timesaver.

It automatically identifies repeated entries and merges duplicate rows in seconds. And the best part? It handles both text and numbers efficiently.

So how does it merge rows based on duplicates?

Tip. You will find Combine Duplicate Rows as part of either Power Tools collection:

or the Remove Duplicates collection:

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.

Tip. No need to sort the table first. The tool finds duplicates automatically whenever they are — even when they appear scattered throughout the sheet.

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 ;)

With these 4 methods, merging duplicates in Google Sheets will become a breeze. Whether you prefer formulas or the fast-track add-on, you now have several options to keep your data organized.

Practice spreadsheet

Combine duplicate rows — formula examples (make yourself a copy)

The above is the detailed content of Combine data from multiple rows into one row based on column value in Google Sheets. For more information, please follow other related articles on the PHP Chinese website!