+ Reply to Thread
Results 1 to 11 of 11

Prevent duplicate entries in Excel 2000

  1. #1
    Stressed
    Guest

    Prevent duplicate entries in Excel 2000

    I'm trying to create data validation to prevent users from entering duplicate
    rows in Excel 2000. I tried this also with conditional formatting to turn the
    newly entered line red, which didn't work either.
    This is a name and address list where I want to check the first name(B),
    last name(C), address line 2(F), address line 1(G), and zip(J) for being a
    duplicate row.
    I highlighted the col., data/validation/settings/custom/and entered formula
    For col. B the formula is
    OR(COUNTIF(C:C,B2)>0,COUNTIF(F:F,B2)>0),COUNTIF(G:G,B2)>0,COUNTIF(J:J,B2)>0)
    For col. C the formula is
    OR(COUNTIF(B:B,C2)>0,COUNTIF(F:F,C2)>0),COUNTIF(G:G,C2)>0,COUNTIF(J:J,C2)>0)
    For col. F the formula is
    OR(COUNTIF(B:B,F2)>0,COUNTIF(C:C,F2)>0),COUNTIF(G:G,F2)>0,COUNTIF(J:J,F2)>0)
    For col. F the formula is
    OR(COUNTIF(B:B,G2)>0,COUNTIF(C:C,G2)>0),COUNTIF(F:F,G2)>0,COUNTIF(J:J,G2)>0)
    For col. F the formula is
    OR(COUNTIF(B:B,J2)>0,COUNTIF(C:C,J2)>0),COUNTIF(F:F,J2)>0,COUNTIF(G:G,J2)>0)
    Checking for >1 had the same result, entering anything returns the error
    message.
    Is it considering blank lines as the duplicates? This will be added to on a
    regular basis so there's no range limit wanted. Please Help and Thanks!

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    What's your intention?

    for B2, for instance, you seem to want to not allow any entry equal to anything in columns C, F, G or J. If that's the case try

    =COUNTIF(C:C,B2)+COUNTIF(F:F,B2)+COUNTIF(G:G,B2)+COUNTIF(J:J,B2)=0

  3. #3
    Peo Sjoblom
    Guest

    Re: Prevent duplicate entries in Excel 2000

    See

    http://www.cpearson.com/excel/NoDupEntry.htm


    --
    Regards,

    Peo Sjoblom

    Portland, Oregon




    "Stressed" <Stressed@discussions.microsoft.com> wrote in message
    news:8658BD91-3FD9-40DE-998D-184910C0E559@microsoft.com...
    > I'm trying to create data validation to prevent users from entering
    > duplicate
    > rows in Excel 2000. I tried this also with conditional formatting to turn
    > the
    > newly entered line red, which didn't work either.
    > This is a name and address list where I want to check the first name(B),
    > last name(C), address line 2(F), address line 1(G), and zip(J) for being a
    > duplicate row.
    > I highlighted the col., data/validation/settings/custom/and entered
    > formula
    > For col. B the formula is
    > OR(COUNTIF(C:C,B2)>0,COUNTIF(F:F,B2)>0),COUNTIF(G:G,B2)>0,COUNTIF(J:J,B2)>0)
    > For col. C the formula is
    > OR(COUNTIF(B:B,C2)>0,COUNTIF(F:F,C2)>0),COUNTIF(G:G,C2)>0,COUNTIF(J:J,C2)>0)
    > For col. F the formula is
    > OR(COUNTIF(B:B,F2)>0,COUNTIF(C:C,F2)>0),COUNTIF(G:G,F2)>0,COUNTIF(J:J,F2)>0)
    > For col. F the formula is
    > OR(COUNTIF(B:B,G2)>0,COUNTIF(C:C,G2)>0),COUNTIF(F:F,G2)>0,COUNTIF(J:J,G2)>0)
    > For col. F the formula is
    > OR(COUNTIF(B:B,J2)>0,COUNTIF(C:C,J2)>0),COUNTIF(F:F,J2)>0,COUNTIF(G:G,J2)>0)
    > Checking for >1 had the same result, entering anything returns the error
    > message.
    > Is it considering blank lines as the duplicates? This will be added to on
    > a
    > regular basis so there's no range limit wanted. Please Help and Thanks!



  4. #4
    Stressed
    Guest

    Re: Prevent duplicate entries in Excel 2000

    This is a mailing list of names and addresses where the input will come from
    various people so there's a probability that a person will be entered more
    than once. I'm trying to check the 5 columns while the data is being input
    and give an error message if "Jane Doe, Apt. 101, 333 Main St, 12456" is
    already on the list. The way I believe it should work is if a person is
    entered twice, as they tab out of the zip column or hit enter on the second
    entry, they will get the "duplicate" error message. Thanks.

    "daddylonglegs" wrote:

    >
    > What's your intention?
    >
    > for B2, for instance, you seem to want to not allow any entry equal to
    > anything in columns C, F, G or J. If that's the case try
    >
    > =COUNTIF(C:C,B2)+COUNTIF(F:F,B2)+COUNTIF(G:G,B2)+COUNTIF(J:J,B2)=0
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=501528
    >
    >


  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Select column J and use this formula in data validation

    =SUMPRODUCT(--(B$1:B$100&C$1:C$100&F$1:F$100&G$1:G$100&J$1:J$100=B1&C1&F1&G1&J1))=1

    extend the range (beyond row 100) if necessary, you can't use whole column references

  6. #6
    Stressed
    Guest

    Re: Prevent duplicate entries in Excel 2000

    Thanks, Peo, but I found that website and tried to adjust it to my needs with
    no luck. What I need is much more complex.

    "Peo Sjoblom" wrote:

    > See
    >
    > http://www.cpearson.com/excel/NoDupEntry.htm
    >
    >
    > --
    > Regards,
    >
    > Peo Sjoblom
    >
    > Portland, Oregon
    >
    >
    >
    >
    > "Stressed" <Stressed@discussions.microsoft.com> wrote in message
    > news:8658BD91-3FD9-40DE-998D-184910C0E559@microsoft.com...
    > > I'm trying to create data validation to prevent users from entering
    > > duplicate
    > > rows in Excel 2000. I tried this also with conditional formatting to turn
    > > the
    > > newly entered line red, which didn't work either.
    > > This is a name and address list where I want to check the first name(B),
    > > last name(C), address line 2(F), address line 1(G), and zip(J) for being a
    > > duplicate row.
    > > I highlighted the col., data/validation/settings/custom/and entered
    > > formula
    > > For col. B the formula is
    > > OR(COUNTIF(C:C,B2)>0,COUNTIF(F:F,B2)>0),COUNTIF(G:G,B2)>0,COUNTIF(J:J,B2)>0)
    > > For col. C the formula is
    > > OR(COUNTIF(B:B,C2)>0,COUNTIF(F:F,C2)>0),COUNTIF(G:G,C2)>0,COUNTIF(J:J,C2)>0)
    > > For col. F the formula is
    > > OR(COUNTIF(B:B,F2)>0,COUNTIF(C:C,F2)>0),COUNTIF(G:G,F2)>0,COUNTIF(J:J,F2)>0)
    > > For col. F the formula is
    > > OR(COUNTIF(B:B,G2)>0,COUNTIF(C:C,G2)>0),COUNTIF(F:F,G2)>0,COUNTIF(J:J,G2)>0)
    > > For col. F the formula is
    > > OR(COUNTIF(B:B,J2)>0,COUNTIF(C:C,J2)>0),COUNTIF(F:F,J2)>0,COUNTIF(G:G,J2)>0)
    > > Checking for >1 had the same result, entering anything returns the error
    > > message.
    > > Is it considering blank lines as the duplicates? This will be added to on
    > > a
    > > regular basis so there's no range limit wanted. Please Help and Thanks!

    >
    >


  7. #7
    Stressed
    Guest

    Re: Prevent duplicate entries in Excel 2000

    Thanks, that looks like what I need but it gives me the same result of always
    giving the "duplicate row" message even when I enter unique information. I
    tried it with > 0 instead of =1 but there were no changes. Any other ideas???

    "daddylonglegs" wrote:

    >
    > Select column J and use this formula in data validation
    >
    > =SUMPRODUCT(--(B$1:B$100&C$1:C$100&F$1:F$100&G$1:G$100&J$1:J$100=B1&C1&F1&G1&J1))=1
    >
    > extend the range (beyond row 100) if necessary, you can't use whole
    > column references
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=501528
    >
    >


  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Hi stressed,

    It certainly worked for me yesterday and today when I tested again, you should only get an error message if all 5 columns are the same for one row, are you using exactly the formula I posted?

    Also you need to apply that formula at row 1, if your applying from row 2 as per your example then alter the refs accordingly

    daddy

  9. #9
    Stressed
    Guest

    Re: Prevent duplicate entries in Excel 2000

    Hi daddylonglegs,

    Here's what I copied into the data validation -
    =SUMPRODUCT(--(B$2:B$500&C$2:C$500&F$2:F$500&G$2:G$500&J$2:J$500=B2&C2&F2&G2&J2))=1

    I have a header on line 1. I get no error messages. Other ways I tried doing
    this, I got error messages for everything. I highlighted column J + went to
    data validation and only have it for that column. I just want to get it
    working on one column, and if it's needed on the others, it will just be a
    copy + paste. We're doing something different, probably something small. Is
    there anything else you're doing?





    "daddylonglegs" wrote:

    >
    > Hi stressed,
    >
    > It certainly worked for me yesterday and today when I tested again, you
    > should only get an error message if all 5 columns are the same for one
    > row, are you using exactly the formula I posted?
    >
    > Also you need to apply that formula at row 1, if your applying from row
    > 2 as per your example then alter the refs accordingly
    >
    > daddy
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=501528
    >
    >


  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Hey Stresssed

    If you selected the whole of column J and then applied that formula in data validation it won't work because the row references will be out of sync (e.g. data validation for J1 will refer to row 2, validation in J2 will refer to row 3 etc.)

    You need to just select J2:J500 and then copy in that formula

  11. #11
    Stressed
    Guest

    Re: Prevent duplicate entries in Excel 2000

    No change, still no error messages. I changed this to 100 rows for the time
    being.
    I placed this in each row in the column's next to J + extered unique +
    duplicate rows.
    =IF(SUMPRODUCT(--(B$2:B$100&C$2:C$100&F$2:F$100&G$2:G$100&J$2:J$100=B2&C2&F2&G2&J2))= 1,TRUE,FALSE)
    The result of this on each row shows 'false'. I copied + pasted one person's
    info to assure I'm not doing typos. also tried it like this + got 'false'
    =IF(SUMPRODUCT(--(B$2:B$100&C$2:C$100&F$2:F$100&G$2:G$100&J$2:J$100=B2&C2&F2&G2&J2)=1),TRUE,FALSE)
    Further I changed the filters as below + got the results listed -
    =IF(SUMPRODUCT(--(B$2:B$100&C$2:C$100&F$2:F$100&G$2:G$100&J$2:J$100=B2&C2&F2&G2&J2)=1),TRUE,FALSE) all false
    =IF(SUMPRODUCT(--(B$2:B$100&C$2:C$100&F$2:F$100&G$2:G$100&J$2:J$100=B2&C2&F2&G2&J2))> 0,TRUE,FALSE) all true
    =IF(SUMPRODUCT(--(B$2:B$100&C$2:C$100&F$2:F$100&G$2:G$100&J$2:J$100=B2&C2&F2&G2&J2)>0),TRUE,FALSE) all false
    =IF(SUMPRODUCT(--(B$2:B$100&C$2:C$100&F$2:F$100&G$2:G$100&J$2:J$100=B2&C2&F2&G2&J2))>1,TRUE,FALSE) all true
    =IF(SUMPRODUCT(--(B$2:B$100&C$2:C$100&F$2:F$100&G$2:G$100&J$2:J$100=B2&C2&F2&G2&J2)>1),TRUE,FALSE) all false
    =IF(SUMPRODUCT(--(B$2:B$100&C$2:C$100&F$2:F$100&G$2:G$100&J$2:J$100=B2&C2&F2&G2&J2))=1,TRUE,FALSE) all false
    =IF(SUMPRODUCT(--(B$2:B$100&C$2:C$100&F$2:F$100&G$2:G$100&J$2:J$100=B2&C2&F2&G2&J2)=1),TRUE,FALSE) all false
    This goes along with my always getting error message or never getting them.
    How is it yours works + mine doesn't??? I'm missing something!!!
    If you can't see anything, can you send me your test excel sheet? I'll look
    at that and compare things - salamander@mail.yellowstone.net. Thanks.

    Stresssssssssssssed


+ 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