identify duplicate enteries

[Guest]

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 Phillips]

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.

 

[Guest]

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]

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

 

[Guest]

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]

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.