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.
ไม่มีความคิดเห็น:
แสดงความคิดเห็น