How to find duplicates values and keep only those rows?

Question

I am checking an huge Open Office Calc list on multiple values in column C.

I have a file like this

A        B      C
jan 1    Joop   35,90
jan 1    Kees   80,10
jan 2    Joep   79,99
jan 4    Kip    14,88
jan 4    Roel   15,66
jan 8    Riep   35,90
jan 9    Jaap   15,66

I would like tho find all the rows which have a value in column C that is a duplicate of another cell from C (so ignore column A and B). I would like to get output like this:

A        B     C 
jan 1    Joop  35,90
jan 4    Roel  15,66
jan 8    Riep  35,90
jan 9    Jaap  15,66

Can you guys help me to achieve this?

Answer 1

Place this formula in column D and copy down. Every 2nd double entry will be marked with 2 (or more), the rest with 1.

Copy Column D, then paste special as text (remove all the other check marks) over the same data in Column D.

Now all the formula's have been turned into fixed data in Column D.

If you only need to do this clean-up once, you can then put 1 in E1 and drag this down on the rightbottom drag-point of cell E1 to create a list of sequence numbers (1,2,3,4..).

Then sort the whole range on column D.

Remove all the rows that have a 2 in column D.

Then resort the whole range on Column E (original sequence numbers)

=COUNTIF(C$1:C1;C1)  

The other option is to use a standard filter

Select the No duplication option, but this is just a filter, the data still remains in your sheet.