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!

Prevent duplicate entries in Excel 2000
 

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

Prevent duplicate entries in Excel 2000
 

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!

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)+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

Prevent duplicate entries in Excel 2000
 

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

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" 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!

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

Prevent duplicate entries in Excel 2000
 

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

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

Prevent duplicate entries in Excel 2000
 

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

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 - . Thanks.

Stresssssssssssssed

Prevent duplicate entries in Excel 2000
 

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!