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

Combining duplicate rows in your spreadsheets may turn to one of the most intricate tasks. Let's see what Google 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

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 in Google Sheets.

CONCATENATE - function and operator to join records

The first thing that comes to mind when I think of bringing duplicate rows together is CONCATENATE function and the ampersand (&) - a special concatenation operator.

Suppose you have a list of movies to watch and you'd like to group them by genre:
Combine rows of movies based on genre.

  • You can merge cells only with spaces between the values:

    =CONCATENATE(B2," ",C2," ",B8," ",C8)
    =B2&" "&C2&" "&B8&" "&C8
    Combine duplicates using spaces.

  • Or use spaces with any other marks to combine duplicate rows together:

    =CONCATENATE(A3,": ",B3," (",C3,"), ",B6," (",C6,") ")
    =A3&": "&B3&" ("&C3&"), "&B6&" ("&C6&") "
    Make use of the ampersand to separate values.

Once the rows are merged, you can get rid of formulas and keep only the text by the example of 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 can work for small datasets, but what to do when they get bigger?

Merge cells yet keep data with UNIQUE + JOIN

This tandem of formulas searches for duplicates for you. However, you are still in charge and have to show the formulas where to look. Let's see how it works on the same to-watch list.

  1. I use UNIQUE in E2 to check for duplicate genres in column A:

    =UNIQUE(A2:A)
    Get rid of repeats in genres.
    The formula returns the list of all genres no matter whether they repeat or don't repeat themselves in the original list. In other words, it removes duplicates from column A.

    Tip. Should you add more values to column A, the formula will expand the list automatically with unique records.
  2. Then I build my next formula with the JOIN function:

    =JOIN(", ",FILTER(B:B,A:A=E2))
    Google Sheets - combine rows with the same value in genre.
    How do the elements of this formula work?

    • FILTER scans column A for all instances of the value in E2. 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))
    Bring years for the corresponding movies together.

So, this option equips Google Sheets with a few functions to combine multiple rows into one based on duplicates. And it happens automatically. Well, almost. I intend to hold the perfect solution back to the very end of the article. But feel free to hop to it right away ;)

QUERY function to remove duplicate lines

There is one more function that helps operate huge tables - QUERY. It may seem a bit tricky at first, but once you learn how to use it, it will become your true companion in spreadsheets.

=QUERY(data, query, [headers])

How does it work:

  • data (required) - the range of your source table.
  • query (required) - a set of commands to determine conditions in order to get specific data.
    Tip. You can get a full list of all commands here.
  • headers (optional) - the number of header rows in your source table.

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

Example 1

I want to get only comic book movies I am yet to watch:

=QUERY(A1:C,"select * where A='Comic Book'")
Google Sheets: use QUERY to remove duplicates.
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 keep track of the weekend box office for the newest movies in theaters:
Weekend box office of the latest movies.
I use QUERY to remove duplicates and count the total sum of money earned per movie for all weekends. I also alphabetize them by genre:

=QUERY(B1:D, "select B,C, SUM(D) group by B,C")
Use QUERY to remove duplicates and consolidate rows.

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

To sort the records by movie instead, I can 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 you keep track of all books that are in stock all over your branches. The list goes up to a thousand books:
Books in store.

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

    =QUERY('Copy of In stock'!A1:D,"select A,B,C,D where A='Rowling'")
    All books written by J.K. Rowling you have available.

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

    =QUERY('In stock'!A1:D,"select A,B,C,D where (A='Rowling' and C contains 'Harry Potter')")
    All Harry Potter books you have.

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

    =QUERY('In stock'!A1:D,"select A,B, sum(D) where (A='Rowling' and C contains 'Harry Potter') group by A,B")
    Merge duplicates based on condition and calculate values.

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

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

The fastest way to combine duplicate rows

When you give up all hope to find a simple solution to combine multiple rows based on duplicates, our add-on for Google Sheets makes a great entrance. :)

Combine Duplicate Rows scans a column with repeated records, merges corresponding values from other columns separating them with delimiters, and consolidates numbers. All at the same time and in a matter of a few mouse clicks!

Remember my list of books in store with a few hundred rows? Let's see how the tool will manage it.

Tip. Since the utility is part of Power Tools, please install it first and go directly to Merge & Combine group:
Merge & Combine group in Power Tools.
Then click the add-on icon to open it:
Combine rows add-on.
  1. Once the add-on is running, select the range where you want to combine duplicate rows:
    Select the range with your data.
  2. Choose those columns that contain repeated values. In my case, they are Last Name and First Name:
    Choose key columns with duplicate records.
  3. The next step lets you decide on the following:
    • columns with values you will bring together
    • ways to combine those records: merge or calculate
    • delimiter for the text you want to merge
    • function for numbers to calculate

    For me, I'd like to have all books belonging to one author brought to one cell and separated by break lines. If any titles repeat themselves, the add-on will show them only once.

    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.
    Choose columns with the values to merge.

  4. Upon adjusting all settings, click Finish. The add-on will do the work and show the message with everything processed in a few seconds:
    Combine Rows result.

The tool has combined duplicate rows in my list of books. Here's a part of how my data looks now:
Data combined from multiple rows into one row based on column value.

Tip. Have a quick look at how I used the add-on:
Combine duplicate rows with the add-on for Google Sheets.

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

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!