Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 crashes loading excel files created Excel 2000 | Excel Discussion (Misc queries) | |||
How do I find duplicate entries in Excel | Excel Discussion (Misc queries) | |||
how to convert GETPIVOTDATA from excel 2000 to excel 2002... | Excel Worksheet Functions | |||
other systems detecting excel 4.0 if excel 2000 is installed | Setting up and Configuration of Excel | |||
Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions |