1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

duplication in a excel

Discussion in 'Business Applications' started by drafter, Sep 2, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. drafter

    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
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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
     
  3. drafter

    drafter Thread Starter

    Joined:
    Nov 21, 2000
    Messages:
    419
    thankyou very much firefytr. That worked like a hotdam
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    No problem. Glad it worked for you. :)

    Take care!
     
  5. drafter

    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
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  7. drafter

    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.
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Oh, that would be much like the first one ...

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

    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
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  11. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
  12. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/269388

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice