Ho to delete every duplicate?

English
#1

Hello

I’m trying to find a filter on how to delete every single item that have a duplicate on a sheet, but I can only found filters to delete the duplicates but keep one entry of those.

What I am trying to do is

imgur.com/eInMCFW DOT png

or

A - A
B - UNIQUE
C - A
A - B
C - C

I want to delete every single data but unique, I don’t want to keep a single entry of A B or C, if there is a duplicate of that data, I want to destroy all of them and keep only the ones that were not duplicated at all

Any idea?

#2

You can use the LO BASIC macro in the sample ODS below by inserting it on your purged sheet and referring to the used range of your original data sheet. You can adjust it however you see fit, of course.

The key idea of this macro is to create a deletion list of every entry in the original data that has a repeat then go about copying each of the non-repeating entries to a new columnar array.

Please see the sample ODS since the macro is a little too long to insert here.

Purge All Repeats.ods (11.8 KB)

Not sure about the LO Error 523 that might pop up. If it does just do a hard recalculate. Any ideas about this? (Hard recalculate defaults to ctrl+shift+F9).

#4

Thing is, I need to keep the columns, not copy to a new one, Just keep UNIQUE in the specific column and row and delete all the others.

#3

At first: I may misinterpret your question or data, but as I see a B in the second column B is/seems not UNIQUE.

In any case I’d suggest to use COUNTIF to count the items in a separate column like

=COUNTIF($A$1:$A$9, A1)+COUNTIF($B$1:$B$9,A1)

You may use then this column for your filter or just sort by this count, then you can delete the lines with count > 1

#5

Unique means something unique, not a B on the second column, could be the letter Z

I need to delete all A B and C’s while keeping Z or UNIQUE or whatever value is not duplicated on any column.

#6

If you want to purge in place then you really do need a macro. You just have to “run” the macro somehow. In the sample ODS the macro is called DoPurge. To run it I’ve put a button on the page just for simplicity. You can also run it from Tools>Macros>Run Macro (which is a pain) or set it into the toolbar or set it up as a keystroke using Tools>Customize>…

I’ve included two ways to “delete” entries. 1) Set the “deleted” cell’s value to “” and 2) Clear the contents of the deleted cell. Oddly, visually, it seems “quicker” to set the value to “”. If you choose to clear contents, please note that you must REM out the setDataArray toward the end of the macro, or the contents will pop back!

Also note that if you use clear contents and you are still editing one of your data cells, that cell won’t be cleared even if it should be. In general, make sure you have selected a cell outside your data before pressing the Do Purge button.

Purge All Repeats In Place.ods (18.0 KB)

BTW: ctrl+Z seems to undo the purge if you want to play around with your data for testing a bit.

#7

At least with 7.2 and 7.3 is possible to filter by color. Doing a conditional format to change background color on duplicates, they can be selected with the filter and deleted, or their rows selecting on the row number.
Test_DeletingDuplicates.ods (8.6 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.