+ Reply to Thread
Results 1 to 3 of 3

want to check while entering number is duplicated with previous entered numbers

  1. #1
    Registered User
    Join Date
    11-08-2008
    Location
    Myanmar
    Posts
    7

    want to check while entering number is duplicated with previous entered numbers

    hope someone can help me.
    I want to check while entering number is duplicated with previous entered numbers or not.
    I mean,
    I want to enter 1 to 600 in Column A randomly. Eg., 23,345,223,5,... From cell A1 to A600.

    Column A
    23
    345
    223
    5

    I first enter 23 in A1 then I enter 345 in A2, then 223 in A3, then 5 in A4, etc...
    While I'm entering 345 in A2, I want to check whether there is 345 in previous cell of column A, ie., A1.
    So also, while I'm entering 223 in A3, I want to check whether there is 223 in previous cell of column A, ie., A1 and A2.
    So also, while I'm entering 5 in A4, I want to check whether there is 5 in previous cell of column A, ie., A1, A2 and A3 ...etc.
    From A1 to A600, I want to check, is there any duplicated number in previous cell or not.

    I will very appreciated to who help me.

    Thanks in advanced.

    Wiki Winn
    Last edited by wikiwinn; 05-09-2009 at 11:37 PM. Reason: SOLVED

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: want to check while entering number is duplicated with previous entered numbers

    Perhaps you want to think about using Data Validation ?

    Based on your question highlight range A1:A600 -> Data -> Validation:

    Set to Custom: =COUNTIF($A$1:$A1,$A1)=1
    Set Error Msg to: Duplicate

    However what happens if you enter 10 into A10 and then enter 10 into A2... based on your example this would not generate an error as A2 would only look to A1:A2 for duplicates, and A10 will not be tested - when entered it was the sole instance of 10.

    Perhaps you want to test the entire range at all times in which case revise the Custom validation to:

    Set to Custom: =COUNTIF($A$1:$A$600,$A1)=1

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: want to check while entering number is duplicated with previous entered numbers

    you could use conditional format
    for a2 and copied to all cells in range
    =AND(A1<>"",A2=A1)
    edit wrong that only tests the cell immediatly above! doh
    try
    =COUNTIF($A$1:A2,A2)>1
    Last edited by martindwilson; 05-09-2009 at 12:29 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1