Delete rows with duplicated values

[Emece]

Hi.

I have a workbook in Excel 2007 with several phone numbers. If the numbers
are duplicated I applied a conditional formatting so as to display them in
red, using the following formula: =COUNTIF($A:$A,$A2)>1. Additionally if the
value is repeated I need to delete one of the rows. Which is the best way to
do this?

Thanks in advance

Regards
Emece.-

 

[muddan madhu]

use helping column

In Cell B2 put this formula and drag it down

=IF(A2=A1,"duplicate","unique")

use filter to delete the duplicate

 

[Pecoflyer]

Hi.

I have a workbook in Excel 2007 with several phone numbers. If th
numbers
are duplicated I applied a conditional formatting so as to display the
in
red, using the following formula: =COUNTIF($A:$A,$A2)>1. Additionall
if the
value is repeated I need to delete one of the rows. Which is the bes
way to
do this?

Thanks in advance

Regards
Emece.-

Supposing your range is in one column Got to Data - Filter - Advance
filter - Check "unique records only" and choose if you want th
resulting place or not - O

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Shane Devenshire]

Hi,

First: Since you are using 2007, when you want to highlight duplicated no
formula is necessary, instead choose Home, Conditional Formatting, Highlight
Cell Ruled, Duplicate Values.

Second: Since you are using 2007, you don't need to create any helper
columns or formulas to remove the duplicates, instead choose Data, Remove
Duplicates.

 

[Emece]

Thanks for your useful reply. After trying it I have one more doubt: what if
I need to delete the entire row for that duplicated value?

Thanks again
Regards
Emece.-

 

[nozzaworld]

I am using excel 2002. I have a row of data, with some duplicated records. I
want to be able to delete ALL duplicates.

e.g.
bob
bob
scott
john

would delete all record of bob leaving just the following:

scott
john

How can I achieve this? Any help will be greatly apprenciated.

Many thanks