Combine Duplicate Rows in Spreadsheets with Power Tools

In a recent blogpost I showed you how to extract an integer from a string in Excel or Google Sheets. You might want to do this when analysing a bunch of URLs that share unique IDs. In the next couple of minutes, I’m going to show you how to merge or combine duplicate IDs without writing any fancy functions.

In our example we will work with a small imaginary sample. You can easily export such reports from Google Analytics directly to Google Sheets or alternatively you can open any spreadsheet from your local drive. In this example we’ve already extracted the integer IDs from the URL.

As a first step, you will want to install the Power Tools add-on to your browser. You can do this with just one click on the following here.

Once you have your spreadsheet open, run Power Tools. As you can see this tool has many useful features, but this time we will use data and combine rows.

Power Tools gives you a wizard that will guide you to the entire process. First select your data range. In our example this will be the whole spreadsheet. You might want to create a copy of the original spreadsheet, just to make sure you don’t do any damage to the data.

Choose the column with the duplicate data. In our example this will be the column with the IDs.

Choose the values you would like to merge and also select an action. For this example we are going to calculate numbers for all columns. For pageviews, we want to sum up the values, but for Avg. Time on Page and Bounce Rate we will need an average.

Once you have everything set up, click finish and let Power Tools do the work. If you have many columns and rows, this might take a while, but don’t worry Power Tools will let you know if the data you want to process is too much. In this case, you want to process columns one-by-one.

So this is it. We use Power Tools every once in a while for reporting, because we like the simplicity. How do you solve this problem in your job? Do you have a better idea on how to do this?

Interested in other topics? Let me know in the comments!