Conditional Formatting for Duplicates
 

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.

Conditional Formatting for Duplicates
 

You could try something like:

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

for row 1 and drag down

Regards

Trevor


"Daren" wrote in message
...
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.

Conditional Formatting for Duplicates
 

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:D,D1)1),"Duplicate
Value","")

for row 1 and drag down

Regards

Trevor


"Daren" wrote in message
...
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.