by 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
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.
- 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.
- 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.
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 this tool 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 the Combine Duplicate Rows yet, use the link below to get it from Google Workspace Marketplace either as part of Remove Duplicates:
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)
28 comments
Right now everything works fine, I just want to be able to overwrite the old data in all the rows rather than merge. Currently I need to figure out a way to delete the old data. It takes the persons ID number takes the newly added row from Jotform and AFTER I sort the data by update time newest on top I can merge the old row in with the new data.
Hello Mark,
For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance as it gives us a better understanding than any text description.
I kindly ask you to shorten the tables to 10-20 rows.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Do not email there. Once you share the file, just confirm by replying to this comment.
I'll look into your task and see if our software can help.
Hello,
I have a lengthy sheet containing columns of First Name, Middle Name and Last Name.
I also have Power Tools subscription.
As I am new to it, please suggest how to combine these three colums for each row so that (in a resulting column) I have each name in full, like First Name+Middle Name+Last Name separated by spaces.
Hello Vaibhav,
You can do this with our Merge Values add-on, it's also part of Power Tools. Please look through this article to learn more: How to merge cells in Google Sheets without formulas
Thanks so much for these clear tips. The UNIQUE, JOIN and FILTER functions were exactly what I was looking for. Perfect examples and clearly explained.
Appreciate your feedback, Jonathan! Glad you found the solution :)
Thank you. This is going to save me a lot of time.
how to fix marge multiple google sheet data I can't first column data properly my data count 1527 but data I get 801
Query I write is below:
=QUERY({'Zahid Jameel'!A2:H;'Haleema Sadia'!A2:H;},"select * where Col1 is not null",0)
Hello Yasir,
For me to be able to help, I need to see your data. Please share an editable copy of your spreadsheet with us directly: support@apps4gs.com
Make sure your formula is included and specify the expected result.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this reply.
Hello Maam, I have been following your blogs for a couple of months now. I am currently pursuing a course for learning Advanced Excel. I need to submit 1 Task File to my faculty.
But the file is very tricky. For the past few days, I have tried my best to crack it, but I couldn't.It's actually a bit different from what l had learnt so far. If you could share some mail-id, I can share the file with you on that. Please help me out. I would be very grateful. Thanks.
Hello Sonali,
Please specify if you work in Google Sheets or Excel and describe your task in a few words. I will see if can help and suggest what to do next.
Maam, I work on Excel. I have one task file, in which I need to work out some calculations. But unable to do so. It's a bit difficult. Need help on that. I could share the file with you.
Sorry, I don't work in Excel and can't help with its tasks. Please find the relevant blog post regarding Excel on our blog. You may find a solution there.
I love this add on. I am use it to merge data coming in from an LMS. Because users are taking several quizzes it is populating a new row for each user for each quiz. Combining the rows based on name is easy and is working well. I am wondering if there is a way to have this auto run each time a new row is added without opening the add on and manually choosing to combine rows. Thanks!
Thank you for your feedback, Kelly!
If it's not too much trouble for you, we would appreciate it if you rate our add-on and share your feedback on Google Workspace Marketplace. It motivates us to provide the best experience for you and helps others understand how our product makes their life easier :)
As for your feature request, I'm afraid it's currently impossible to process data with this tool automatically. The closest the add-on offers at the moment is the ability to save the settings you use most often into scenarios that you can reuse in a couple of clicks whenever you need.
Hi,
I've created a google form where members can record the wine they are sending to be stored.
The form can have up to 6 wines. The responses are recorded in an excel sheet.
On the same row I would get membership number, name, wine 1, wine 2 and so on
I've created a sheet where i have used "Data Validation" for a drop down list of the membership number
once i select the membership number, i automatically have cells using "Match" that fill with name and list of wine stored. The list of wine stored now appear in column because I use "Transpose Data"
Because the member can fill in the form several times, i end up with various rows with the same membership number, and more different wines
The issue I have is that when I select the membership number from the drop down, I would only get the wines from the first row identified as matching with the membership wines, but I would not get the other wines.
Of course an easy way would be to simply filter by membership number but I need to print it out, in a certain layout, without certain data and with additional standard information
I feel I should follow your tutorial where you can get everything by genre but I'm struggling with the drop down function I'd like to keep.
Is there a way to do this before I give up something I've spend the day on T_T
Thank you
Hi,
For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
To grant us access to your data, press the Share button at the upper right corner of Google Sheets and enter support@apps4gs.com
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.
I'll look into your task and do my best to help.
Thank you Natalia, I've just shared the file. Hope this helps clarifying the problem I have.
Thiky
Thank you for the file, Thiky, it helped a lot!
I put the formula into B8 on the Print Log sheet, please take a look:
=QUERY({FLATTEN(QUERY({'Form responses 1'!B2:K9},"select Col5, Col6, Col7, Col8, Col9, Col10 where Col1="&$C$5,0))},"select * where Col1 is not null",0)
Thank you so so much Natalia. I just saw this!
My pleasure, Thiky!
I have approximately 1922 rows- maybe 40 columns for a particular sheet.. I am trying to pull data (diagnosis dates) that populates in several different columns depending on (type) for each individual row.. I want to pull the dates into one column. Injury details tab-
https://docs.google.com/spreadsheets/d/1JUs1FC126BiPEMXIvqoyLMVB7gvPmpHpKZ8e0pZzHRk/edit
Hello Erin,
It's quite difficult to offer a certain solution since I don't fully understand how the result should look like. I can only suggest you try the following add-ons:
If the tools don't help much, please add to your spreadsheet an example sheet illustrating how your result should look like. I'll see if there's a better solution.
Hi,
I wanted another solution,
I wanted to merge the 1st cell and keep the rest of the values as it is
eg a. 1. ron
a. 2. ray
b. 1. ron
b. 2. ray
and the output that i want is
here the a should be combined across 2 rows
is it possible.
Hello Shipra,
Please specify what exactly the result should look like.
Just had to say thank you for this! Been nagging me for so long how to do this simply, without all the macros, etc. that others online suggest! Beautiful solution.
=QUERY('Copy of In stock'!A1:D,"select A,B,C,D where A='Rowling'")
*[In the where section I want to matching by A cell link ( where A3 ).
Is it possible?
Hello Rakibur,
If you need to pull data based on the content of A3, here's the formula:
=QUERY(A1:G11,"select A,B,C,D where A= '"&A2&"' ")
A cell reference must be wrapped in ampersands, then double quotes, then single quotes.