I have 4 distinct columns that need to be anlalyzed for duplicate data using
conditional formats. I want to write a function that will look at each value
in the columns and determine if there are duplicates and overlap in times.
Column A has serial number, column B has city name, and column C and column D
have times. I want to find duplicates in column A and also in columns B, C
and D to check for any errors. For example, in the first record column A has
0011, column B has Chicago in it, column C has 10:45 in it, and column D has
11:30 in it. In the second record, column A has 0011, column B has Chicago
in it, column C has 11:00, and column D has 12:00 in it. As you can see, the
values in A are the same, which is not supposed to occur, so I know I would
need a conditional format for that. But there is also duplication in the
city name, and finally, overlap in the times. Is there a formula/conditional
format I can use to look at all these 4 data items and only sort it once to
bring all errors to the top? Thanks.

You could try something like:

=IF(OR(COUNTIF(A:A,A1)>1,COUNTIF(B:B,B1)>1,COUNTIF(C:C,C1)>1,COUNTIF(D,D1)>1),"Duplicate
Value","")

for row 1 and drag down

Regards

Trevor


"Daren" <(E-Mail Removed)> wrote in message
news:6AFDD357-D7CF-4BF7-80E8-(E-Mail Removed)...
>I have 4 distinct columns that need to be anlalyzed for duplicate data
>using
> conditional formats. I want to write a function that will look at each
> value
> in the columns and determine if there are duplicates and overlap in times.
> Column A has serial number, column B has city name, and column C and
> column D
> have times. I want to find duplicates in column A and also in columns B,
> C
> and D to check for any errors. For example, in the first record column A
> has
> 0011, column B has Chicago in it, column C has 10:45 in it, and column D
> has
> 11:30 in it. In the second record, column A has 0011, column B has
> Chicago
> in it, column C has 11:00, and column D has 12:00 in it. As you can see,
> the
> values in A are the same, which is not supposed to occur, so I know I
> would
> need a conditional format for that. But there is also duplication in the
> city name, and finally, overlap in the times. Is there a
> formula/conditional
> format I can use to look at all these 4 data items and only sort it once
> to
> bring all errors to the top? Thanks.

Trevor,
I tried your formula but if picked up duplicates in every cell when I copied
it down for every record. I don't think it's working properly because it
says Duplicate Value even when there is no such duplicate value. Another
suggestion I've heard is to make copies of the worksheet once finished
running macros. I will try that. Thanks for your help.

"Trevor Shuttleworth" wrote:

> You could try something like:
>
> =IF(OR(COUNTIF(A:A,A1)>1,COUNTIF(B:B,B1)>1,COUNTIF(C:C,C1)>1,COUNTIF(D,D1)>1),"Duplicate
> Value","")
>
> for row 1 and drag down
>
> Regards
>
> Trevor
>
>
> "Daren" <(E-Mail Removed)> wrote in message
> news:6AFDD357-D7CF-4BF7-80E8-(E-Mail Removed)...
> >I have 4 distinct columns that need to be anlalyzed for duplicate data
> >using
> > conditional formats. I want to write a function that will look at each
> > value
> > in the columns and determine if there are duplicates and overlap in times.
> > Column A has serial number, column B has city name, and column C and
> > column D
> > have times. I want to find duplicates in column A and also in columns B,
> > C
> > and D to check for any errors. For example, in the first record column A
> > has
> > 0011, column B has Chicago in it, column C has 10:45 in it, and column D
> > has
> > 11:30 in it. In the second record, column A has 0011, column B has
> > Chicago
> > in it, column C has 11:00, and column D has 12:00 in it. As you can see,
> > the
> > values in A are the same, which is not supposed to occur, so I know I
> > would
> > need a conditional format for that. But there is also duplication in the
> > city name, and finally, overlap in the times. Is there a
> > formula/conditional
> > format I can use to look at all these 4 data items and only sort it once
> > to
> > bring all errors to the top? Thanks.
>
>
>