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!

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: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=501528

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)+C OUNTIF(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

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

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

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

See

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


--
Regards,

Peo Sjoblom

Portland, Oregon




"Stressed" wrote in message
...
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!

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" wrote in message
...
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!

Hello - thanks so much - http://www.cpearson.com/excel/NoDupEntry.htm
worked nice for my basic validation needs. (Excel 2007).

"Stressed" wrote:

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" wrote in message
...
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!