identify duplicate enteries

Is there a way with out having to physically line uo the columns to compare
two or more columns for identifing and flagging any duplicate entrires of
ethier text or numbers

Use conditional formatting with a formula in one column of say
=COUNTIF(H:H,A1)>0, and in the other =COUNTIF(A:A,H1)>0, and a suitable
pattern colour. All duplicates in either column will then be highlighted.

--
HTH

Bob Phillips

"Debi" <Debi@discussions.microsoft.com> wrote in message
news:9A618802-7344-489F-8087-05AFAB64CC91@microsoft.com...
> Is there a way with out having to physically line uo the columns to
compare
> two or more columns for identifing and flagging any duplicate entrires of
> ethier text or numbers

Bob,
Thanks for the response. I am unclear as to how this would work. Could you
be more specific? Lets say there are three columns A C E
Column A - B7936, B7933, B6790, k34a9, ddoo2. 1234
Column B - TF121, TF123, ddoo2, 1345, b6790
Column C - 1245, 1288, 1597, 5555, 8376, 1234
Using this type of data how would I compare all three columns and identify
any duplication

"Bob Phillips" wrote:

> Use conditional formatting with a formula in one column of say
> =COUNTIF(H:H,A1)>0, and in the other =COUNTIF(A:A,H1)>0, and a suitable
> pattern colour. All duplicates in either column will then be highlighted.
>
> --
> HTH
>
> Bob Phillips
>
> "Debi" <Debi@discussions.microsoft.com> wrote in message
> news:9A618802-7344-489F-8087-05AFAB64CC91@microsoft.com...
> > Is there a way with out having to physically line uo the columns to
> compare
> > two or more columns for identifing and flagging any duplicate entrires of
> > ethier text or numbers
>
>
>

Okay, step by step

Select column A
Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula =OR(COUNTIF(B:B,A1)>0,COUNTIF(C:C,A1)>0)
Select Format
Select the Pattern tab
Choose an effective colour
OK
OK

Select column B
Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula =OR(COUNTIF(A:A,B1)>0,COUNTIF(C:C,B1)>0)
Select Format
Select the Pattern tab
Choose an effective colour
OK
OK

Select column C
Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula =OR(COUNTIF(A:A,C1)>0,COUNTIF(B:B,C1)>0)
Select Format
Select the Pattern tab
Choose an effective colour
OK
OK

--
HTH

Bob Phillips

"Debi" <Debi@discussions.microsoft.com> wrote in message
news:8DB58164-5C07-439C-9D27-1C157B2721F3@microsoft.com...
> Bob,
> Thanks for the response. I am unclear as to how this would work. Could you
> be more specific? Lets say there are three columns A C E
> Column A - B7936, B7933, B6790, k34a9, ddoo2. 1234
> Column B - TF121, TF123, ddoo2, 1345, b6790
> Column C - 1245, 1288, 1597, 5555, 8376, 1234
> Using this type of data how would I compare all three columns and identify
> any duplication
>
> "Bob Phillips" wrote:
>
> > Use conditional formatting with a formula in one column of say
> > =COUNTIF(H:H,A1)>0, and in the other =COUNTIF(A:A,H1)>0, and a suitable
> > pattern colour. All duplicates in either column will then be
highlighted.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Debi" <Debi@discussions.microsoft.com> wrote in message
> > news:9A618802-7344-489F-8087-05AFAB64CC91@microsoft.com...
> > > Is there a way with out having to physically line uo the columns to
> > compare
> > > two or more columns for identifing and flagging any duplicate entrires
of
> > > ethier text or numbers
> >
> >
> >

Bob,
Thanks, you have been a fantastic help. If I could inquire one last thing,
in your instructions you use B:B,A1 and C:C,A1 and A:A,B1 and so forth. I do
not mean to come across total ignorant however I feel it. I believe I
understand the A:A and B:B and C:C tell it to look in all of the column no
matter how many rows have data and if there are rows that are empty the
syntax tell it to continue looking. Is that correct? However what does the
syntax A1, B1, and C1 represent.
I greatly appreciate you taking the time to answer what I'm sure is a
question whose syntax must seem to be an easy write for you.

Respectfully,
Debi A Angel

"Bob Phillips" wrote:

> Okay, step by step
>
> Select column A
> Format>Conditional Formatting
> Change Condition 1 to Formula Is
> Add a formula =OR(COUNTIF(B:B,A1)>0,COUNTIF(C:C,A1)>0)
> Select Format
> Select the Pattern tab
> Choose an effective colour
> OK
> OK
>
> Select column B
> Format>Conditional Formatting
> Change Condition 1 to Formula Is
> Add a formula =OR(COUNTIF(A:A,B1)>0,COUNTIF(C:C,B1)>0)
> Select Format
> Select the Pattern tab
> Choose an effective colour
> OK
> OK
>
> Select column C
> Format>Conditional Formatting
> Change Condition 1 to Formula Is
> Add a formula =OR(COUNTIF(A:A,C1)>0,COUNTIF(B:B,C1)>0)
> Select Format
> Select the Pattern tab
> Choose an effective colour
> OK
> OK
>
> --
> HTH
>
> Bob Phillips
>
> "Debi" <Debi@discussions.microsoft.com> wrote in message
> news:8DB58164-5C07-439C-9D27-1C157B2721F3@microsoft.com...
> > Bob,
> > Thanks for the response. I am unclear as to how this would work. Could you
> > be more specific? Lets say there are three columns A C E
> > Column A - B7936, B7933, B6790, k34a9, ddoo2. 1234
> > Column B - TF121, TF123, ddoo2, 1345, b6790
> > Column C - 1245, 1288, 1597, 5555, 8376, 1234
> > Using this type of data how would I compare all three columns and identify
> > any duplication
> >
> > "Bob Phillips" wrote:
> >
> > > Use conditional formatting with a formula in one column of say
> > > =COUNTIF(H:H,A1)>0, and in the other =COUNTIF(A:A,H1)>0, and a suitable
> > > pattern colour. All duplicates in either column will then be
> highlighted.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Debi" <Debi@discussions.microsoft.com> wrote in message
> > > news:9A618802-7344-489F-8087-05AFAB64CC91@microsoft.com...
> > > > Is there a way with out having to physically line uo the columns to
> > > compare
> > > > two or more columns for identifing and flagging any duplicate entrires
> of
> > > > ethier text or numbers
> > >
> > >
> > >
>
>
>

Debi,

The A1,B1,C1 is just referring to the first cell being tested. Excel will
'magically' update that cell for each cell in the selection , so A2 will
have the formula =OR(COUNTIF(B:B,A2)>0,COUNTIF(C:C,A2)>0) etc. This means
that it is that cell that is tested to see if it exists elsewhere.


--
HTH

Bob Phillips

"Debi" <Debi@discussions.microsoft.com> wrote in message
news:60643415-EE28-46F5-A86B-5992EF96AA7F@microsoft.com...
> Bob,
> Thanks, you have been a fantastic help. If I could inquire one last thing,
> in your instructions you use B:B,A1 and C:C,A1 and A:A,B1 and so forth. I
do
> not mean to come across total ignorant however I feel it. I believe I
> understand the A:A and B:B and C:C tell it to look in all of the column no
> matter how many rows have data and if there are rows that are empty the
> syntax tell it to continue looking. Is that correct? However what does the
> syntax A1, B1, and C1 represent.
> I greatly appreciate you taking the time to answer what I'm sure is a
> question whose syntax must seem to be an easy write for you.
>
> Respectfully,
> Debi A Angel
>
> "Bob Phillips" wrote:
>
> > Okay, step by step
> >
> > Select column A
> > Format>Conditional Formatting
> > Change Condition 1 to Formula Is
> > Add a formula =OR(COUNTIF(B:B,A1)>0,COUNTIF(C:C,A1)>0)
> > Select Format
> > Select the Pattern tab
> > Choose an effective colour
> > OK
> > OK
> >
> > Select column B
> > Format>Conditional Formatting
> > Change Condition 1 to Formula Is
> > Add a formula =OR(COUNTIF(A:A,B1)>0,COUNTIF(C:C,B1)>0)
> > Select Format
> > Select the Pattern tab
> > Choose an effective colour
> > OK
> > OK
> >
> > Select column C
> > Format>Conditional Formatting
> > Change Condition 1 to Formula Is
> > Add a formula =OR(COUNTIF(A:A,C1)>0,COUNTIF(B:B,C1)>0)
> > Select Format
> > Select the Pattern tab
> > Choose an effective colour
> > OK
> > OK
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Debi" <Debi@discussions.microsoft.com> wrote in message
> > news:8DB58164-5C07-439C-9D27-1C157B2721F3@microsoft.com...
> > > Bob,
> > > Thanks for the response. I am unclear as to how this would work. Could
you
> > > be more specific? Lets say there are three columns A C E
> > > Column A - B7936, B7933, B6790, k34a9, ddoo2. 1234
> > > Column B - TF121, TF123, ddoo2, 1345, b6790
> > > Column C - 1245, 1288, 1597, 5555, 8376, 1234
> > > Using this type of data how would I compare all three columns and
identify
> > > any duplication
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Use conditional formatting with a formula in one column of say
> > > > =COUNTIF(H:H,A1)>0, and in the other =COUNTIF(A:A,H1)>0, and a
suitable
> > > > pattern colour. All duplicates in either column will then be
> > highlighted.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Debi" <Debi@discussions.microsoft.com> wrote in message
> > > > news:9A618802-7344-489F-8087-05AFAB64CC91@microsoft.com...
> > > > > Is there a way with out having to physically line uo the columns
to
> > > > compare
> > > > > two or more columns for identifing and flagging any duplicate
entrires
> > of
> > > > > ethier text or numbers
> > > >
> > > >
> > > >
> >
> >
> >