Find and Remove Duplicate Rows in Google Docs

F
Published in: Google Docs

Unlike Microsoft Excel, there aren’t any Filter functions available in Google Docs spreadsheets but you can easily emulate them with simple formulae. Let’s see how:

Filter Unique Records in Google Docs

If you like to remove duplicate rows from a table in Google Docs, use the UNIQUE formula as shown in this screencast.

unique-records

Click an empty cell, type =UNIQUE(, select the range of cells you want to filter and then close the parenthesis. Simple.

Find Duplicate Rows in Google Docs

This is a reverse case where you want to display rows in a spreadsheet that have duplicates.

find duplicates

This requires a couple of steps:

Step 1: Assuming that our original data is in columns A, B & C, go to cell D1 and write a formula to concatenate the data in three columns. We’ll use a pipe separator to distinguish between a row like “a1, b, c” and “a, 1b, c”.

\=CONCATENATE(A1, ”|”, B1, ”|”, C1) – drag the cell handle to fill the formula in other cells.

Step 2: Sort the D column by clicking the header as shown in the screencast.

Step 3: We now need a function to compare two adjacent values in column D. If the values are same, one of them is a duplicate for sure. Type this in E2:

\=IF(D2=D1, “Duplicate”, "") – drag to fill all cells until E5.

That’s it. All rows in column E that have value “Duplicate” are duplicate rows.

Related: Google Docs Guide

Published in: Google Docs

Looking for something? Find here!

Meet the Author

Web Geek, Tech Columnist
A
Amit Agarwal

Amit Agarwal is a Google Developer Expert in GSuite and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India. Read more on Lifehacker and YourStory

Get in touch