|
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" wrote in message ... 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" wrote in message ... 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 FormatConditional 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 FormatConditional 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 FormatConditional 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" wrote in message ... 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" wrote in message ... 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 FormatConditional 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 FormatConditional 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 FormatConditional 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 FormatConditional 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 FormatConditional 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 FormatConditional 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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com