Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Prevent Duplicate Entries (Excel 2003)

    Hi

    Is there a way using data validation to prevent the same number being entered into a column.

    Lets Column B, if B2 contains L-456 then it cannot be entered in any othe cell in a range of Cells in column B.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent Duplicate Entries (Excel 2003)

    Does <post:=402,054>post 402,054</post:> help?

    Steve

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent Duplicate Entries (Excel 2003)

    Yes, you can do that with data validation. Suppose you want unique entries in columns B2:B1000.
    Select the range B2:B1000,
    then choose Data | Validation...
    Allow: Custom
    Formula: =COUNTIF($B$2:$B$1000,B2)=1
    and put an appropriate message in the error alert
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent Duplicate Entries (Excel 2003)

    Braddy

    Assuming your Column of data is in A.

    Click--> Format|Conditional Formatting

    Change "Cell value is " to "Formula is"

    Type in =IF(COUNTIF(A:A, A1)>1,TRUE,FALSE) where A1 is the first cell in the range to check. Change the pattern to be Red and press OK

    Now when you type in a vduplicate value the cell will turn red.....Enjoy
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prevent Duplicate Entries (Excel 2003)

    Your formula will work, but

    =COUNTIF(A:A,A1)>1

    is the exact equivalent to

    =IF(COUNTIF(A:A,A1)>1,TRUE,FALSE)

    for a logical condition returns TRUE or FALSE.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent Duplicate Entries (Excel 2003)

    Yes indeed....this function was rattling around a text file of things I learnt in Woody's on my work PC. <img src=/S/note.gif border=0 alt=note width=20 height=20> ...must update more often <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent Duplicate Entries (Excel 2003)

    Also, it would be better to use a smaller subset of the column rather than the entire column: lots of overhead there.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent Duplicate Entries (Excel 2003)

    Yep, thanks. I chucked in A:A but I know Braddy has been around a bit to adapt his code to fit the range he is working on.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I think I will keep away from Excel from now on, I seem to have had a lot of comment about my assistance of lately <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>
    Jerry

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prevent Duplicate Entries (Excel 2003)

    > I think I will keep away from Excel from now on

    That's nonsense, your contributions are very welcome. If someone points out that a formula could be shorter or more efficient, it's meant in a constructive way. It happens to me too: your formula in <post:=533,805>post 533,805</post:> is much more elegant than my convoluted effort higher up in that thread. I actually enjoy it if someone comes up with a better solution than mine - I learn from the Lounge too!

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent Duplicate Entries (Excel 2003)

    > I think I will keep away
    That's nonsense: we all have the challenge of beating Hans to the solution. <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Prevent Duplicate Entries (Excel 2003)

    Hi Steve

    I must apologise, I do tend to get a bit confused sometimes.

    Again Thanks to All

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent Duplicate Entries (Excel 2003)

    No need to apologize. We all get confused at times...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •