Conditional format for duplicate text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GillW - 02 Oct 2007 15:05 GMT

Hi,

I have been given a spreadsheet with about 16,000 rows of data, each
line including an alpha-numeric reference number. I have been asked to
easily flag up duplicates and have tried to do this with conditional
formatting using

=COUNTIF(Criteria!$A$1:$A$16000,A1)>1

but it seems this is for numbers only. Can anyone help a simple soul
like me get this straight so that rows with duplicated References (for
example "G092307223812W" ) can be easily picked out?

Much appreciated

Reply to this Message

GillW - 02 Oct 2007 15:07 GMT

> Hi,
>
[quoted text clipped - 10 lines]
>
> Much appreciated

Sorry - I forgot to say I'm using Excel 2002

Reply to this Message

Dave Peterson - 02 Oct 2007 16:17 GMT

This should work for text, too.

If you had trouble, you should post more details.

And personally, I'd use the same kind of formula in a new column (insert a new
column B). With 16000 rows, scrolling through the data looking for differences
in formatting doesn't appeal to me.

I'd apply data|filter|autofilter to that helper column so that I could filter to
see True's.

> Hi,
>
[quoted text clipped - 10 lines]
>
> Much appreciated

Signature

Dave Peterson

Reply to this Message