How to Flag duplicate values in column

[Ruth]

I'm wondering how search for and flag duplicates in a column of data.
Basically what I'd like to do is create a formula that looks at the value in
an adjacent cell and tells me that value exists anywhere else in the column
of values eg something like...


=if(E3 exists somewhere else in column E), E3,"")

anyway to do this with just a formula?

TimeTraveller

 

[Roger Govier]

Hi Ruth
Try
=IF(COUNTIF(E:E,E3)>1,E3,"")

 

[josh.clifford]

I'm wondering how search for and flag duplicates in a column of data.
Basically what I'd like to do is create a formula that looks at the value in
an adjacent cell and tells me that value exists anywhere else in the column
of values eg something like...

=if(E3 exists somewhere else in column E), E3,"")

anyway to do this with just a formula?

TimeTraveller

Under the Data menu, you can choose Filter, Autofilter and then check
the box to filter for unique records only. Alternatively, you could
sort the column in question as ascending, and then run an if statement
such as; if(a2=a1,1,0). Anything that shows up as a 1 is a duplicate.

 

[T. Valko]

Try this:

=IF(COUNTIF(D$3$10,D3)>1,D3,"")

Copy down.

If you're using Excel 2007 you can use conditional formatting to highlught
duplicates or uniques.

Home tab>Styles>Conditional Formatting>Highlight Cells Rules>Duplicate
Values

 

[ilia]

You can use conditional formatting in 2003 too. Just use the COUNTIF
part of Biff's formula, as the highlight condition, and copy it to all
cells in question using format painter.

 

[Ruth]

Thanks to all who took the time to respond. I knew I'd seen that somewhere
on my way to something else...thanks for the reminder

TimeTraveller

 

[Emmanuel Garcia]

Is it still as simple using Excel 2007? It appears to me that one extra
click is now required. Do you agree?

--
Regards from Los Angeles,

Emmanuel
Microsoft Windows Vista
Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.4 GHz 3GB RAM
NVidia GeForce 8500 GT 512 MB

I'm wondering how search for and flag duplicates in a column of data.
Basically what I'd like to do is create a formula that looks at the value
in
an adjacent cell and tells me that value exists anywhere else in the
column
of values eg something like...

=if(E3 exists somewhere else in column E), E3,"")

anyway to do this with just a formula?

TimeTraveller

Under the Data menu, you can choose Filter, Autofilter and then check
the box to filter for unique records only. Alternatively, you could
sort the column in question as ascending, and then run an if statement
such as; if(a2=a1,1,0). Anything that shows up as a 1 is a duplicate.

 

[Evgeny Grischenko]

Hello!
I usually use this small add-in for processing dups in spreadsheets:
http://www.office-excel.com/excel-addins/remove-duplicates-excel.html

Best regards,
Eugene