OpenOffice.org Calc is a free spreadsheet application that mimics many of the features of Microsoft Excel. Unlike Excel, Calc does not feature an easy way to identify and remove duplicate rows from a spreadsheet. Instead, you can use Calc's sort features and formulas to manually identify duplicate rows in your spreadsheet. After your duplicate rows are identified, you can remove, isolate or mark the rows to identify them as duplicates.
Open your spreadsheet. Select all the cells in the data range.
Select "Sort" from the "Data" menu. Sort the data in your spreadsheet by the column that you want to check for duplicate values. If the column containing duplicate values is column A, for example, sort the data by column A.
Select the first empty cell in the first row; for example, D1. You will use this cell's column to identify duplicate values in column A. Type "=IF(A1=A2;1;0)" without quotes into the cell and press "Enter." This formula will check for duplicate rows and display a "1" in the cell if a row is duplicated.
Select D1 and press "Ctrl" and "C" to copy the data. Highlight the D column from D1 to the last row in your data range. Press "Ctrl" and "V" to paste the formula into each cell. All duplicate rows will now be marked with a "1."
Select the D column. Press "Ctrl" and "C" to copy the data. Press "Ctrl," "Shift" and "V" to open the "Paste Special" dialogue. Select the "Numbers" option and deselect all other options. Press "OK." This will allow you to sort the data in your table by the D column to quickly identify the duplicate rows.
Select your entire data range again, including the D column. Select "Sort" from the "Data" menu. Sort your data by the D column and choose "Descending." All duplicate rows will appear at the top of your spreadsheet.
Attempting to sort your data by column D without performing the "Paste Special" in Step 5 will change the data contained in the D cells, giving you incorrect data.
Tips and warnings
- Attempting to sort your data by column D without performing the "Paste Special" in Step 5 will change the data contained in the D cells, giving you incorrect data.