Difference between revisions of "Documentation/How Tos/Removing Duplicate Rows"
From Apache OpenOffice Wiki
< Documentation  How Tos
m (Removed the "Additionally you can use <code>=IF(A1=A2;1;IF(A2=A1;1;0))</code> To indicate all duplicates", because it is just a more complicated formula with the same result) 
m (→Removing Duplicate Rows in Calc) 

Line 1:  Line 1:  
== Removing Duplicate Rows in Calc ==  == Removing Duplicate Rows in Calc ==  
−  Q: ''How do I select all unique values from a column in an  +  Q: ''How do I select all unique values from a column in an {{AOo}} Calc spreadsheet? I want to remove all redundant rows from a report with thousands of records.'' 
A: There is no automatic function to remove duplicate rows. Follow these steps to delete all rows that have duplicate values in column A:  A: There is no automatic function to remove duplicate rows. Follow these steps to delete all rows that have duplicate values in column A:  
Line 8:  Line 8:  
#:On most systems, you can click any cell inside the data range, then press Ctrl+Multiplication key on the numeric keypad.  #:On most systems, you can click any cell inside the data range, then press Ctrl+Multiplication key on the numeric keypad.  
#Sort the data range by column A.  #Sort the data range by column A.  
−  #:Choose Data  +  #:Choose {{menuDataSort}}. 
#Click an empty cell in the first row. Let's assume it is cell C1. Enter the formula:  #Click an empty cell in the first row. Let's assume it is cell C1. Enter the formula:  
#: <code>=IF(A1=A2;1;0)</code><! this code only works for the first duplicate and always misses the last duplicate if there is more than 2 records >  #: <code>=IF(A1=A2;1;0)</code><! this code only works for the first duplicate and always misses the last duplicate if there is more than 2 records >  
Line 15:  Line 15:  
#:Drag the lower right edge of the cell C1 down to the last row.  #:Drag the lower right edge of the cell C1 down to the last row.  
#Now the formulas must be replaced by their values to freeze the contents.  #Now the formulas must be replaced by their values to freeze the contents.  
−  #:While the column C is still highlighted, press Ctrl  +  #:While the column C is still highlighted, press {{keyCtrlC}} to copy all selected cells to the clipboard. 
−  #Press Shift  +  #Press {{keyShiftCtrlV}} to open the Paste Special dialog box. 
−  #:In the Selection area, enable only the Numbers command; disable the other Selection commands. Click OK.  +  #:In the Selection area, enable only the Numbers command; disable the other Selection commands. Click {{buttonOK}}. 
#Select the whole data range including the new column C and sort the range by column C.  #Select the whole data range including the new column C and sort the range by column C.  
−  #:Choose Data  +  #:Choose {{menuDataSort}}. 
−  #Select all rows which have a value 1 in column C, then press Del key.  +  #Select all rows which have a value 1 in column C, then press {{keyDel}} key. 
#Optional steps: Delete column C. Select the remaining rows and sort them again by column A.  #Optional steps: Delete column C. Select the remaining rows and sort them again by column A.  
Revision as of 14:54, 18 May 2022
Removing Duplicate Rows in Calc
Q: How do I select all unique values from a column in an Apache OpenOffice Calc spreadsheet? I want to remove all redundant rows from a report with thousands of records.
A: There is no automatic function to remove duplicate rows. Follow these steps to delete all rows that have duplicate values in column A:
 Select all cells of the current data range.
 On most systems, you can click any cell inside the data range, then press Ctrl+Multiplication key on the numeric keypad.
 Sort the data range by column A.
 Choose Data → Sort.
 Click an empty cell in the first row. Let's assume it is cell C1. Enter the formula:

=IF(A1=A2;1;0)
 This will display 1 if the current row has the same value in column A as the next row. It will display 0 if the values are different.

 Copy the formula down for all rows of the data range.
 Drag the lower right edge of the cell C1 down to the last row.
 Now the formulas must be replaced by their values to freeze the contents.
 While the column C is still highlighted, press Ctrl + C to copy all selected cells to the clipboard.
 Press ⇧ Shift + Ctrl + V to open the Paste Special dialog box.
 In the Selection area, enable only the Numbers command; disable the other Selection commands. Click OK .
 Select the whole data range including the new column C and sort the range by column C.
 Choose Data → Sort.
 Select all rows which have a value 1 in column C, then press Del key.
 Optional steps: Delete column C. Select the remaining rows and sort them again by column A.
Tip: This method can also be used to remove duplicate text lines from a Writer document. Copy the text to a Calc spreadsheet. Remove the duplicates, then copy and paste back as "unformatted text" to Writer.
Content on this page is licensed under the Public Documentation License (PDL). 