In column C, there are duplicate values that I don't want. How do I go about filtering the rows by the condition - Show the rows where there are duplicate values in column C?

CodePudding user response：

I would add a helper column at the end with =COUNTIF(C:C;C1) and copy it down behind every row with data. Then filter on >1.

Or you can create a pivottable and put column C in the values as a count.

CodePudding user response：

Easiest way is to add conditional formatting for duplicates and then filter on colour - it doesn't involve changing your data set at all and is an easy visual cue as to where duplicates exist. However, similar to the answer above, this will filter out both the original and the duplicate record.

If you want one of the rows to show (i.e. 1 of each value in column C), then a new column with the COUNTIF formula applied to the range starting 1 cell below or above would work. So if the new column was "D", in cell "D2", you would use either;

Cell above: = COUNTIF($C$1:$C1,$C2)

OR

Cell below: = COUNTIF($C3:$C$9000,$C2)

Note: if you use the count from the cell below (i.e. to show the last row where that value is found), you will need to assign a "finish row" - I used 9000, but you could go to 1048576 if you want to include whole sheet.

You would then filter column D to only show 0s

These solutions assume you still want to keep the records, just hide them. If this is not the case, there are easy ways to delete duplicates.

If this answers your question, please upvote and tick as solved.