duplication in a excel

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

 

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

 

thankyou very much firefytr. That worked like a hotdam

 

No problem. Glad it worked for you.

Take care!

 

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

 

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.

 

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.

 

Oh, that would be much like the first one ...

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

 

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

 

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.

 

Here is a good working example of what you want to do

http://www.j-walk.com/ss/excel/usertips/tip073.htm



Rollin