Ask Your Question
0

Finding and copying unique values from a column -duplicates values

asked 2016-09-09 07:03:38 +0200

dimzev gravatar image

I have sheet libreoffice calc with thousands of unique values in one column. From this sheet each time must removed a few hundred. that's what I do : 1)Add the records that I want to remove at the end of the column. 2) formats the duplicate records with a different color

and I want : 1)with a filter i want to only see the duplicate records so i can copy or delete this records 2)and I get the unique registration and copy them to a different sheet

I would like to thank anyone give me his time and the help me

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-09-11 10:50:33 +0200

Regina gravatar image

Assign a name to the entire data range in Data > Define Range. Thereby open the Options and check the needed options, likely Contains column labels and Insert or delete cells. That way you can use a named filter and do not conflict with other filters.

After the range is named, select it, e.g. by Data > Select Range. Then use Data > More Filters > Standard Filters. Use the Condition = and the value Not Empty for those columns, which should become duplicate-free, connect with operator AND. That way, the condition should be always true. (I assume, that you do not have empty cells in the range.)

Open the Options and check No duplications and in Copy results to you enter the address of the top,left cell of the target range for the duplicate-free data. OK

If your source data has changes you can update the duplicate-free data by selecting the source range and then using Data> Refresh Range.

edit flag offensive delete link more

Comments

Thanks for toyr answer!!! I think your answer is too complex .... I prefer to add a column and add COUNTIF(D:D;D1) ..it is simple and see not only duplicates ...

dimzev gravatar imagedimzev ( 2017-09-12 12:13:11 +0200 )edit
1

answered 2016-09-09 09:40:37 +0200

JohnSUN gravatar image

updated 2016-09-09 09:49:00 +0200

You can use this trick

Delete ununique

You say "must removed a few hundred". Width of sheet is 1024 columns. Is it enough?

(detailed instructions in Russian see here)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-09 07:03:38 +0200

Seen: 1,522 times

Last updated: Sep 11 '17