Removing Duplicate Rows in Calc
How do I select all unique values from a column in an OOo Calc spreadsheet? I want to remove all redundant rows from a report with thousands of records.
There is no automatic function to remove duplicate rows. Follow these steps to delete all rows that have duplicate values in column 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 . - 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 . - 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.
Posted by Noah on August 09, 2006 at 03:43 PM CEST #
I would like to add if you want to check for duplicate rows where more than 1 column matches, you can do etc Thanx once again.
Posted by Richard on December 05, 2006 at 09:45 PM CET #
Posted by Alex Deriziotis on January 12, 2007 at 02:04 PM CET #
Posted by Ufi on January 12, 2007 at 04:24 PM CET #
Posted by Shane R. Spencer on March 20, 2007 at 12:18 AM CET #
Posted by Josh on April 09, 2007 at 04:34 PM CEST #
The "No Duplicate" option was very usefull. Thanks!
Posted by Gabe on August 16, 2007 at 08:45 PM CEST #
Careful about this, removing duplicates and generating a unique list are two different things here. This assumes that you'll always have at least 1 duplicate pair. For example;
[CODE]|[RES],
A120|1,
A120|0,
A121|0,
A122|1,
A122|0,
..So, sorting by RES and deleting where RES=0, will delete A121.
Posted by Nick on October 26, 2007 at 07:56 AM CEST #
Great help... thanks for the stright forward setps!
Posted by Rick Denison on April 23, 2008 at 07:29 PM CEST #
I was trying to figure out how to sort all this data between 2 spreadsheets removing duplicates. Thanks for the formula help!
Posted by Jeremy K. on June 26, 2008 at 01:40 AM CEST #
for fancy footwork on rows and columns, like removing duplicates etc. in openorg calc save in csv, go to http://home.hccnet.nl/s.j.francke/software/software.htm and download and run "CSVed for editing your csv files..." and Bob (snr) is your uncle.
enjoy, I did!
Posted by Gabriël Smit on September 26, 2008 at 12:33 AM CEST #
I was able to get as far as pasting the result of the formula in a new column as number only. However, Calc conked out on me when I tried to sort by that column, deleting most of the rows. This was on a spreadsheet with 4200 rows of data. I think maybe it was too much for it, I am running a very powerful machine so I can't put the blame on the memory or processor. I was using OpenOffice 2.4.1
Posted by Joel on October 03, 2008 at 10:43 PM CEST #
The above tip shows a "1" in the results cells if there is a duplicate and "0" if not. However, it can be confusing looking down a column looking at 1s and 0s, but you can change the "0" to blanks via Format > Cell > Numbers > Leading zeros and change this to 0. You can then look down the results column and just see a "1" for duplicate lines.
Posted by Mark on October 08, 2008 at 06:52 PM CEST #
Of course, with a bit of modification, you can have the spreadsheet copy the rows for you. For example:
=IF(A1=A2;"";A1)
That copies the contents of A1 into the new row, and if it is a duplicate, it leaves it blank. Then I selected my new row, edit/copy, then clicked on another column and edit/paste special, unclick "copy all", unclick "copy formula", and then I had a new row with unique vales. Sort that new row, and you only get the unique values.
Posted by Guy Schalnat on October 12, 2008 at 04:22 AM CEST #
I found this advice useful:
http://www.openofficetips.com/blog/archives/2004/12/basic_functions_7.html
"You can use the Standard Filter to remove duplicates.
Select the range
Use Data -> Filter -> Standard Filter
Set test that is always true
Select More
Select Copy results to
Select No duplication"
Posted by Stef on October 20, 2008 at 08:19 AM CEST #
That doesn't seem user friendly at all. How cool would it be to have an option "remove duplicates" while sorting data? It's a pretty common task judging form the Google search "Calc remove duplicates".
Frank
http://vizualbod.com
Posted by Frank on October 25, 2008 at 12:00 AM CEST #
This shows up in the search, so a couple of notes for OOo version 3.0:
1. The formula, as stated, was giving me Err:508, I had to use commas instead of semi-colons.
2. Using another method linked from the comments (Data->Filter etc.) with some (large enough?) datasets is unpredictable for some reason. I was working on a CSV file with 35K rows and the filter method was leaving me with only about 16K records. On the other hand, the method described in this blog left about 27K records. The latter was correct, so use the filter at your own risk.
Posted by zd on February 17, 2009 at 11:16 PM CET #
That commas versus semicolons issue is a known Ubuntu bug, see https://bugs.launchpad.net/ubuntu/+source/openoffice.org/+bug/306602
Posted by ufi on February 18, 2009 at 09:16 AM CET #
Also check this out. I have two columns. One is a newer data set and I need to filter it against the older one that I have completed actions for. So, I used a vlookup and it worked beautifully. It makes the completed items and I can sort on the column with the vlookup up in it.
Here is a GREAT link to help with it. http://www.openofficeschool.org/calc/vlookup/
Posted by Trevor on April 29, 2009 at 10:38 PM CEST #
This was a great help. I didn't want to automatically remove ALL duplicates, rather, be able to do a sort, then see all the duplicates together, and manually delete those duplicates I wanted... SO, i needed to be able to have BOTH duplicates return 1, and all other rows return 0, so that the duplicates could be grouped... here is the formula that worked for me:
=IF(B2=B3;1;IF(B2=B1;1;0))
Posted by Amos on October 08, 2009 at 11:09 PM CEST #