Difference between revisions of "Documentation/How Tos/Removing Duplicate Rows"
From Apache OpenOffice Wiki
< Documentation | How Tos
m (→Removing Duplicate Rows in Calc) |
m (→Removing Duplicate Rows in Calc) |
||
(6 intermediate revisions by 4 users not shown) | |||
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: | ||
#Select all cells of the current data range. | #Select all cells of the current data range. | ||
− | #:On most systems, you can click any cell inside the data range, then press Ctrl | + | #:On most systems, you can click any cell inside the data range, then press {{key|Ctrl|*}} key on the numeric keypad. |
#Sort the data range by column A. | #Sort the data range by column A. | ||
− | #:Choose Data | + | #:Choose {{menu|Data|Sort}}. |
#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> | + | #: <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 --> |
#: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. | #: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. | #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. | #: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 {{key|Ctrl|C}} to copy all selected cells to the clipboard. |
− | #Press Shift | + | #Press {{key|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. | + | #:In the Selection area, enable only the Numbers command; disable the other Selection commands. Click {{button|OK}}. |
#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 {{menu|Data|Sort}}. |
− | #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 {{key|Del}} 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. | ||
Line 29: | Line 26: | ||
{{PDL1}} | {{PDL1}} | ||
+ | |||
+ | [[Category:Calc]] | ||
+ | [[Category:Documentation/How Tos/Calc]] |
Latest revision as of 14:55, 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 + * 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). |