software

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)gt;0,COUNTIF(F:F,B2)gt;0),COUNTIF(G: G,B2)gt;0,COUNTIF(J:J,B2)gt;0)
For col. C the formula is
OR(COUNTIF(B:B,C2)gt;0,COUNTIF(F:F,C2)gt;0),COUNTIF(G: G,C2)gt;0,COUNTIF(J:J,C2)gt;0)
For col. F the formula is
OR(COUNTIF(B:B,F2)gt;0,COUNTIF(C:C,F2)gt;0),COUNTIF(G: G,F2)gt;0,COUNTIF(J:J,F2)gt;0)
For col. F the formula is
OR(COUNTIF(B:B,G2)gt;0,COUNTIF(C:C,G2)gt;0),COUNTIF(F: F,G2)gt;0,COUNTIF(J:J,G2)gt;0)
For col. F the formula is
OR(COUNTIF(B:B,J2)gt;0,COUNTIF(C:C,J2)gt;0),COUNTIF(F: F,J2)gt;0,COUNTIF(G:G,J2)gt;0)
Checking for gt;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!


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) C OUNTIF(J:J,B2)=0--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=501528See

www.cpearson.com/excel/NoDupEntry.htm--
Regards,

Peo Sjoblom

Portland, Oregon

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

quot;daddylonglegsquot; wrote:

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


Select column J and use this formula in data validation

=SUMPRODUCT(--(B$1:B$100amp;C$1:C$100amp;F$1:F$100amp;G$1:G$100amp;J$1:J$100 =B1amp;C1amp;F1amp;G1amp;J1))=1

extend the range (beyond row 100) if necessary, you can't use whole
column references--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=501528Thanks, Peo, but I found that website and tried to adjust it to my needs with
no luck. What I need is much more complex.

quot;Peo Sjoblomquot; wrote:

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

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

quot;daddylonglegsquot; wrote:

gt;
gt; Select column J and use this formula in data validation
gt;
gt; =SUMPRODUCT(--(B$1:B$100amp;C$1:C$100amp;F$1:F$100amp;G$1:G$100amp;J$1:J$100 =B1amp;C1amp;F1amp;G1amp;J1))=1
gt;
gt; extend the range (beyond row 100) if necessary, you can't use whole
gt; column references
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=501528
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=501528Hi daddylonglegs,

Here's what I copied into the data validation -
=SUMPRODUCT(--(B$2:B$500amp;C$2:C$500amp;F$2:F$500amp;G$2:G$500amp;J$2:J$500 =B2amp;C2amp;F2amp;G2amp;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?quot;daddylonglegsquot; wrote:

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


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--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=501528