PDA

View Full Version : How to only keep duplicate values



RKramkowski
04-20-2010, 07:27 AM
Hi, I have a large spreadsheet (90,000 rows, 12 columns). I can use conditional highlighting to see the duplicate values in one column (say column B). But I want to keep only those rows that have duplicate values in column B. There are functions built into Excel to remove dupes. But I want to do the inverse. Anyone know how I could do this?

thanks,
Bob

p45cal
04-20-2010, 07:45 AM
Temporarily insert a new column A
Put a formula in A2 (assumes you have headers in row 1):
=IF(COUNTIF($C$2:$C$90000,C2)=1,"x","")
(adjust to suit your ranges)
and copy down to the end.
Now those rows marked with an x are unique entries, you want to lose them, so Autofilter for them and delete what you see.
Remove Autofilter, remove column A

GTO
04-20-2010, 07:50 AM
...answered...