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.
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.
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
Find this article at: http://www.labnol.org/software/find-remove-duplicate-records-google-docs/5169/
Tags: feature, google docs, microsoft excel, screencasts, Software
Reader Comments
Great tip, thanks! What software did you use to create the animated gif screen captures? The gifs are so small and convey a great deal of info in a small file.
Written by Michael on 10.31.08
The tip about finding unique rows is really nice, and will be very useful.
However, the tip about finding duplicate rows is more of a typical Excel work around – it kind of dilutes the ‘amazingness’ of the first tip. Further, the duplicate row tip can only work for adjacent rows.
Written by atulmathur on 10.31.08