Merging duplicate rows in your spreadsheets may turn into 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. Show
Functions to combine cells with the same value in Google SheetsYou 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 recordsThe first thing that comes to mind when I think of not simply removing duplicates but bringing duplicate rows together is Google Sheets CONCATENATE function and an ampersand (&) – a special concatenation operator. Suppose you have a
list of movies to watch and you'd like to group them by genre:
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 + JOINThis tandem of formulas 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 how it works on the same to-watch list.
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 in Google SheetsThere 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. Here's the QUERY function itself: =QUERY(data, query, [headers]) How does it work:
To put it simply, Google Sheets QUERY returns some sets of values based on the conditions you specify. Example 1I want to get only comic book movies I am yet to watch:
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 2Suppose 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 sum of money earned per movie for all weekends. I also alphabetize them by genre:
Note. For the group by command, you must enumerate all columns after select, otherwise, the formula won't work. To sort records by movie instead, I can simply change the order of columns for the group by:
Example 3Let'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 hundreds of books:
I guess for now you've got an idea of how the QUERY function "removes 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 queries it uses and the rules of applying them, the function won't be much of a help. The fastest way to combine duplicate rowsWhen 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 cells from other columns, separates these records 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 the Merge & Combine group: Then click the add-on icon to
open it:
The tool has combined duplicate rows in my list of books. Here's a part of how my data looks now: Tip. Have a quick look at how I used the add-on: Or watch a short video introducing the tool: Use scenarios to semi-automate merging duplicatesAnother possibility Combine Duplicate Rows offers is to semi-automate its use. If you often go through the steps and select the same options, you can save them into scenarios. Scenarios let you reuse the same settings effortlessly on the same or different datasets. You will need to give your scenario a name & specify a sheet and a range it should process: 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 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 inHow do I group rows with the same value in Google Sheets?Keyboard Shortcut to Group Rows in Google Sheets: Alt + Shift + ->. Select the cells that you want to group (A2:A4 in this example). With the cells selected, hold the ALT and SHIFT keys and press the right arrow key. (Mac users hold Option instead of Alt). Select the Option Group rows 2-4.. Hit Enter.. How do I combine rows with the same name?From the Toolbar, select Data > Consolidate.. A dialogue box pops up.. We can choose different Functions.. Now select the data range by keeping the key column in the leftmost.. After that press Add to add the references.. Tick on the Top row & Left column and press OK.. How do I combine multiple rows into one with duplicates?How to merge duplicate rows in Excel. On Step 1 select your range.. On Step 2 choose the key columns with duplicate records.. On Step 3 indicate the columns with the values to merge and choose demiliters.. All the duplicates are merged according to the key columns.. |