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!

## Bookmarks