Prevent duplicate entries in Excel 2000

[Stressed]

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!

 

[daddylonglegs]

What's your intention?

for B2, for instance, you seem to want to not allow any entry equal t
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)=

 

[Peo Sjoblom]

See

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


--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Stressed]

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]

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

 

[Stressed]

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.

 

[Stressed]

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]

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

 

[Stressed]

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]

Hey Stresssed

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

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

 

[Stressed]

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 - (e-mail address removed). Thanks.

Stresssssssssssssed

 

[Tony B]

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