Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How to Flag duplicate values in column

4,766 views
Skip to first unread message

Ruth

unread,
Jan 27, 2008, 12:03:35 AM1/27/08
to
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


josh.c...@yahoo.com

unread,
Jan 27, 2008, 1:46:05 AM1/27/08
to

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

unread,
Jan 27, 2008, 2:08:31 AM1/27/08
to
Try this:

=IF(COUNTIF(D$3:D$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

--
Biff
Microsoft Excel MVP


"Ruth" <now...@null.net> wrote in message
news:HgUmj.16094$ow.13857@pd7urf1no...

ilia

unread,
Jan 27, 2008, 3:46:45 PM1/27/08
to
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.


On Jan 27, 2:08 am, "T. Valko" <biffinp...@comcast.net> wrote:
> Try this:
>
> =IF(COUNTIF(D$3:D$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
>
> --
> Biff
> Microsoft Excel MVP
>

> "Ruth" <nowh...@null.net> wrote in message

Roger Govier

unread,
Jan 27, 2008, 1:35:39 AM1/27/08
to
Hi Ruth
Try
=IF(COUNTIF(E:E,E3)>1,E3,"")

--

Regards
Roger Govier

"Ruth" <now...@null.net> wrote in message
news:HgUmj.16094$ow.13857@pd7urf1no...

Ruth

unread,
Jan 27, 2008, 10:00:52 PM1/27/08
to
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

"Ruth" <now...@null.net> wrote in message
news:HgUmj.16094$ow.13857@pd7urf1no...

Emmanuel Garcia

unread,
Jan 28, 2008, 2:57:52 AM1/28/08
to
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


<josh.c...@yahoo.com> wrote in message
news:1774c150-7029-4ae8...@z17g2000hsg.googlegroups.com...

Evgeny Grischenko

unread,
Feb 1, 2008, 1:54:01 PM2/1/08
to
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


"Ruth" <now...@null.net> wrote in message
news:HgUmj.16094$ow.13857@pd7urf1no...

0 new messages