Excel 2007 : How to keep ONE instance of duplicates while removing duplicates AND unique values

**cjh_2012** · 03-28-2012, 04:13 PM

How can I remove duplicates and unique values all in one go?
Example:

Before:
A
C
D
E
B
B
D

After:
B
D

Any help is appreciated.

**Dennis7849** · 03-28-2012, 04:37 PM

In other words, you want to keep only one instance of any value that repeats?

**cjh_2012** · 03-28-2012, 04:39 PM

Yes, that's what I'm looking for.

**tom1977** · 03-28-2012, 04:43 PM

look at attachment
empty cells delete manually

**martindwilson** · 03-28-2012, 05:52 PM

@tom1977
thats a strange if

=IF(COUNTIF($A$2:$A2,A2)=2,$A$2:$A2,"")

should be
=IF(COUNTIF($A$2:$A2,A2)=2,A2,"")

**tom1977** · 03-28-2012, 06:01 PM

true strange

but results are the same

**Dennis7849** · 03-29-2012, 12:16 AM

See attached modified workbook.

How to keep ONE instance of duplicates while removing duplicates AND unique values