duplication in a excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

drafter

Thread Starter
Joined
Nov 21, 2000
Messages
419
I have built a spread sheet (excel 2000) where I enter drawing numbers and I already have thousands of numbers in it. I would like to know when I duplicate a number in a column, I do not want to use a data base. I would like the cell to turn a different color when there is a duplication. There may even be circumstances where the number is duplicated 4 or 5 times, I would like them all to turn a different color. Can anyone help me with this.

thanx
 
Joined
Jul 25, 2004
Messages
5,458
Sure,

Select the entire column that your entries are in, we'll say it's column A for this example. Go to Format -> Conditional Formatting -> Formula is ...

=COUNTIF($A:$A,$A1)>1

Set your format. Note: This will highlight ALL duplicate occurances, including the first one. HTH
 

drafter

Thread Starter
Joined
Nov 21, 2000
Messages
419
I have another question for you firefytr. What if I want columns A and B to match. for example if cell A1 was dog and cell B1 was cat I would like to find a match like Cell A100 was dog and B100 was cat, and it would hightlight all the cells. Is this possible.

thanx
 
Joined
Jul 25, 2004
Messages
5,458
Sure,

You'd highlight columns A and B, go to conditional formatting again, Formula is...

=($A1=$B1)*($A1<>"")

The first condition ($A1=$B1) checks for matches, then so you don't highlight all your blank cells we add an AND condition (w/ the * sign, the + sign would be for an OR logical operation) we add the second part ($A1<>"").

If you're adding this to a range of cells w/ prior conditional formatting you may want to be careful.
 

drafter

Thread Starter
Joined
Nov 21, 2000
Messages
419
Thats not quite what I meant. The way the fomula you sent me looks for a match in A column and B column. I want it to find a match when cell A and cell B are the same as another Cell in A or B column for example; lets say cell A1 is "123" and cell B1 is "456" and cell A5 is "123" and cell B5 is "456" then all 4 cell would highlighted.
The way it is working now is when there is a match in cell A1 and cell B1 it highlight them both. Sorry if my explantions arent very good.
 

drafter

Thread Starter
Joined
Nov 21, 2000
Messages
419
Something is still not quite working right. I took a little bit of basic programming so maybe if I explain it in a formula it might make more sense to you. I would write it like this if "a1=a2 and b1=b2 then highlight"

thanx again
 
Joined
Jul 25, 2004
Messages
5,458
This will highlight the upper row of this occurance...

=(($A1=$A2)*($B1=$B2)*(A1<>""))

It would be a little trickier to highlight the bottom row also, and might be best done with VBA.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top