|
How to check previous cells for same data and return Y/N
Hi everyone,
I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
I'm a bit confused. Which column would you use for the check? Suppose
you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48*pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
That's exactly right. Sorry I wasn't clearer.
"Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
Do you want it to just be flagged? Or would you prefer to prevent the entry
in the first place? -- John C "Emily" wrote: That's exactly right. Sorry I wasn't clearer. "Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
I want it to be flagged (I have a column called "Repeat?" and within that
column I want it to automatically populate Yes or No). Thanks. "John C" wrote: Do you want it to just be flagged? Or would you prefer to prevent the entry in the first place? -- John C "Emily" wrote: That's exactly right. Sorry I wasn't clearer. "Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
Assuming your repeat column is column T, and your data begins in row 2.
Type the formulas given below into the following cells T2: =IF(OR(E2="",L2="",S2=""),"",IF(OR(E2=L2,E2=S2,L2= S2),"Yes","No")) T3: =IF(OR(E3="",L3="",S3=""),"",IF(OR(COUNTIF($E$2:$E 2,$E3)+COUNTIF($L$2:$L2,$E3)+COUNTIF($S$2:$S2,$E3) 0,COUNTIF($E$2:$E3,$L3)+COUNTIF($L$2:$L2,$L3)+COU NTIF($S$2:$S2,$L3)0,COUNTIF($E$2:$E3,$S3)+COUNTIF ($L$2:$L3,$S3)+COUNTIF($S$2:$S2,$S3)0),"Yes","No" )) Copy the formula in T3 down as needed. -- John C "Emily" wrote: I want it to be flagged (I have a column called "Repeat?" and within that column I want it to automatically populate Yes or No). Thanks. "John C" wrote: Do you want it to just be flagged? Or would you prefer to prevent the entry in the first place? -- John C "Emily" wrote: That's exactly right. Sorry I wasn't clearer. "Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
Thanks, John. I followed your instructions exactly but it's only populating
Yes or No in a few of the cells. Why would it skip some? Thanks. "John C" wrote: Assuming your repeat column is column T, and your data begins in row 2. Type the formulas given below into the following cells T2: =IF(OR(E2="",L2="",S2=""),"",IF(OR(E2=L2,E2=S2,L2= S2),"Yes","No")) T3: =IF(OR(E3="",L3="",S3=""),"",IF(OR(COUNTIF($E$2:$E 2,$E3)+COUNTIF($L$2:$L2,$E3)+COUNTIF($S$2:$S2,$E3) 0,COUNTIF($E$2:$E3,$L3)+COUNTIF($L$2:$L2,$L3)+COU NTIF($S$2:$S2,$L3)0,COUNTIF($E$2:$E3,$S3)+COUNTIF ($L$2:$L3,$S3)+COUNTIF($S$2:$S2,$S3)0),"Yes","No" )) Copy the formula in T3 down as needed. -- John C "Emily" wrote: I want it to be flagged (I have a column called "Repeat?" and within that column I want it to automatically populate Yes or No). Thanks. "John C" wrote: Do you want it to just be flagged? Or would you prefer to prevent the entry in the first place? -- John C "Emily" wrote: That's exactly right. Sorry I wasn't clearer. "Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
First and foremost, is there an entry in every column of each row? In other
words, if I am entering data on row 57, will I enter something in E57, L57, and S57? -- John C "Emily" wrote: Thanks, John. I followed your instructions exactly but it's only populating Yes or No in a few of the cells. Why would it skip some? Thanks. "John C" wrote: Assuming your repeat column is column T, and your data begins in row 2. Type the formulas given below into the following cells T2: =IF(OR(E2="",L2="",S2=""),"",IF(OR(E2=L2,E2=S2,L2= S2),"Yes","No")) T3: =IF(OR(E3="",L3="",S3=""),"",IF(OR(COUNTIF($E$2:$E 2,$E3)+COUNTIF($L$2:$L2,$E3)+COUNTIF($S$2:$S2,$E3) 0,COUNTIF($E$2:$E3,$L3)+COUNTIF($L$2:$L2,$L3)+COU NTIF($S$2:$S2,$L3)0,COUNTIF($E$2:$E3,$S3)+COUNTIF ($L$2:$L3,$S3)+COUNTIF($S$2:$S2,$S3)0),"Yes","No" )) Copy the formula in T3 down as needed. -- John C "Emily" wrote: I want it to be flagged (I have a column called "Repeat?" and within that column I want it to automatically populate Yes or No). Thanks. "John C" wrote: Do you want it to just be flagged? Or would you prefer to prevent the entry in the first place? -- John C "Emily" wrote: That's exactly right. Sorry I wasn't clearer. "Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
Emily,
I think this will do it for you - I've assumed your check column is X, so put this in X2 and copy down: =IF(OR((COUNTIF(E:E,E2)+COUNTIF(L:L,E2)+COUNTIF(S: S,E2)+COUNTIF(A2:S2,E2))2, (COUNTIF(L:L,L2)+COUNTIF(E:E,L2)+COUNTIF(S:S,L2)+C OUNTIF(A2:S2,L2))2, (COUNTIF(S:S,S2)+COUNTIF(L:L,S2)+COUNTIF(E:E,S2)+C OUNTIF(A2:S2,S2))2),"Yes","No") Actually, looking at it now, it could go into any column to the right of S. Hope this helps. Pete On Aug 1, 8:09*pm, Emily wrote: Thanks, John. I followed your instructions exactly but it's only populating Yes or No in a few of the cells. Why would it skip some? Thanks. "John C" wrote: Assuming your repeat column is column T, and your data begins in row 2. Type the formulas given below into the following cells T2: =IF(OR(E2="",L2="",S2=""),"",IF(OR(E2=L2,E2=S2,L2= S2),"Yes","No")) T3: =IF(OR(E3="",L3="",S3=""),"",IF(OR(COUNTIF($E$2:$E 2,$E3)+COUNTIF($L$2:$L2,$*E3)+COUNTIF($S$2:$S2,$E3 )0,COUNTIF($E$2:$E3,$L3)+COUNTIF($L$2:$L2,$L3)+CO U*NTIF($S$2:$S2,$L3)0,COUNTIF($E$2:$E3,$S3)+COUNT IF($L$2:$L3,$S3)+COUNTIF($S*$2:$S2,$S3)0),"Yes"," No")) Copy the formula in T3 down as needed. -- John C "Emily" wrote: I want it to be flagged (I have a column called "Repeat?" and within that column I want it to automatically populate Yes or No). Thanks. "John C" wrote: Do you want it to just be flagged? Or would you prefer to prevent the entry in the first place? -- John C "Emily" wrote: That's exactly right. Sorry I wasn't clearer. "Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again.- Hide quoted text - - Show quoted text - |
How to check previous cells for same data and return Y/N
Nope, there are some blank rows and blank cells dispersed throughout.
"John C" wrote: First and foremost, is there an entry in every column of each row? In other words, if I am entering data on row 57, will I enter something in E57, L57, and S57? -- John C "Emily" wrote: Thanks, John. I followed your instructions exactly but it's only populating Yes or No in a few of the cells. Why would it skip some? Thanks. "John C" wrote: Assuming your repeat column is column T, and your data begins in row 2. Type the formulas given below into the following cells T2: =IF(OR(E2="",L2="",S2=""),"",IF(OR(E2=L2,E2=S2,L2= S2),"Yes","No")) T3: =IF(OR(E3="",L3="",S3=""),"",IF(OR(COUNTIF($E$2:$E 2,$E3)+COUNTIF($L$2:$L2,$E3)+COUNTIF($S$2:$S2,$E3) 0,COUNTIF($E$2:$E3,$L3)+COUNTIF($L$2:$L2,$L3)+COU NTIF($S$2:$S2,$L3)0,COUNTIF($E$2:$E3,$S3)+COUNTIF ($L$2:$L3,$S3)+COUNTIF($S$2:$S2,$S3)0),"Yes","No" )) Copy the formula in T3 down as needed. -- John C "Emily" wrote: I want it to be flagged (I have a column called "Repeat?" and within that column I want it to automatically populate Yes or No). Thanks. "John C" wrote: Do you want it to just be flagged? Or would you prefer to prevent the entry in the first place? -- John C "Emily" wrote: That's exactly right. Sorry I wasn't clearer. "Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
Okay. My formula in T3 was assuming that you had data entered in every column
in every cell going down. This is the modified formula for T3 and down: =IF(OR(IF($E3="",0,IF(COUNTIF($E$2:$E2,$E3)+COUNTI F($L$2:$L2,$E3)+COUNTIF($S$2:$S2,$E3)0,1,0)),IF($ L3="",0,IF(COUNTIF($E$2:$E3,$L3)+COUNTIF($L$2:$L2, $L3)+COUNTIF($S$2:$S2,$L3)0,1,0)),IF($S3="",0,IF( COUNTIF($E$2:$E3,$S3)+COUNTIF($L$2:$L3,$S3)+COUNTI F($S$2:$S2,$S3)0,1,0))),"Yes","No") I am also basing the assumption that you only want the YES to appear when it is repeated, not the first time entered. So if I enter 4713 in cell L24, and then again in cell S46, my value in T24 will still be NO, but my value in T46 will be YES. If you are looking for a yes ANYWHERE there is a repeat, either above or below, see the response by Pete_UK. -- John C "Emily" wrote: Nope, there are some blank rows and blank cells dispersed throughout. "John C" wrote: First and foremost, is there an entry in every column of each row? In other words, if I am entering data on row 57, will I enter something in E57, L57, and S57? -- John C "Emily" wrote: Thanks, John. I followed your instructions exactly but it's only populating Yes or No in a few of the cells. Why would it skip some? Thanks. "John C" wrote: Assuming your repeat column is column T, and your data begins in row 2. Type the formulas given below into the following cells T2: =IF(OR(E2="",L2="",S2=""),"",IF(OR(E2=L2,E2=S2,L2= S2),"Yes","No")) T3: =IF(OR(E3="",L3="",S3=""),"",IF(OR(COUNTIF($E$2:$E 2,$E3)+COUNTIF($L$2:$L2,$E3)+COUNTIF($S$2:$S2,$E3) 0,COUNTIF($E$2:$E3,$L3)+COUNTIF($L$2:$L2,$L3)+COU NTIF($S$2:$S2,$L3)0,COUNTIF($E$2:$E3,$S3)+COUNTIF ($L$2:$L3,$S3)+COUNTIF($S$2:$S2,$S3)0),"Yes","No" )) Copy the formula in T3 down as needed. -- John C "Emily" wrote: I want it to be flagged (I have a column called "Repeat?" and within that column I want it to automatically populate Yes or No). Thanks. "John C" wrote: Do you want it to just be flagged? Or would you prefer to prevent the entry in the first place? -- John C "Emily" wrote: That's exactly right. Sorry I wasn't clearer. "Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again. |
How to check previous cells for same data and return Y/N
Pete and John,
Thank you for saving my butt. -Emily "Pete_UK" wrote: Emily, I think this will do it for you - I've assumed your check column is X, so put this in X2 and copy down: =IF(OR((COUNTIF(E:E,E2)+COUNTIF(L:L,E2)+COUNTIF(S: S,E2)+COUNTIF(A2:S2,E2))2, (COUNTIF(L:L,L2)+COUNTIF(E:E,L2)+COUNTIF(S:S,L2)+C OUNTIF(A2:S2,L2))2, (COUNTIF(S:S,S2)+COUNTIF(L:L,S2)+COUNTIF(E:E,S2)+C OUNTIF(A2:S2,S2))2),"Yes","No") Actually, looking at it now, it could go into any column to the right of S. Hope this helps. Pete On Aug 1, 8:09 pm, Emily wrote: Thanks, John. I followed your instructions exactly but it's only populating Yes or No in a few of the cells. Why would it skip some? Thanks. "John C" wrote: Assuming your repeat column is column T, and your data begins in row 2. Type the formulas given below into the following cells T2: =IF(OR(E2="",L2="",S2=""),"",IF(OR(E2=L2,E2=S2,L2= S2),"Yes","No")) T3: =IF(OR(E3="",L3="",S3=""),"",IF(OR(COUNTIF($E$2:$E 2,$E3)+COUNTIF($L$2:$L2,$Ā*E3)+COUNTIF($S$2:$S2,$E 3)0,COUNTIF($E$2:$E3,$L3)+COUNTIF($L$2:$L2,$L3)+C OUĀ*NTIF($S$2:$S2,$L3)0,COUNTIF($E$2:$E3,$S3)+COU NTIF($L$2:$L3,$S3)+COUNTIF($SĀ*$2:$S2,$S3)0),"Yes ","No")) Copy the formula in T3 down as needed. -- John C "Emily" wrote: I want it to be flagged (I have a column called "Repeat?" and within that column I want it to automatically populate Yes or No). Thanks. "John C" wrote: Do you want it to just be flagged? Or would you prefer to prevent the entry in the first place? -- John C "Emily" wrote: That's exactly right. Sorry I wasn't clearer. "Pete_UK" wrote: I'm a bit confused. Which column would you use for the check? Suppose you entered data into S10 - you want to check that S10 is not contained anywhere in columns E, L or S and neither in A10:S10? Do you want this flagged up in a cell on row 10 somewhere? Then if you enter some data in E15, you want the same checks but reported on row 15? Pete On Aug 1, 5:48 pm, Emily wrote: Hi everyone, I posted a similar question the other day and it was answered, but now my formula is getting more complicated and no amount of playing around with it is giving me what I want. Column E, Column L, and Column S contain a list of text data, which happens to be numbers (e.g. 1172, 4721, 6743, etc.). Every time I enter a new number in the columns, I would like another column in the same row to check the previous entries in all three columns AND the same row and report whether the exact same number has been entered before (if true="yes", if false="no"). Can anyone give me a hand? Thanks again.- Hide quoted text - - Show quoted text - |
How to check previous cells for same data and return Y/N
Thanks for feeding back, Emily.
Pete On Aug 1, 8:45*pm, Emily wrote: Pete and John, Thank you for saving my butt. -Emily |
| All times are GMT +1. The time now is 10:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com