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.

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.

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.

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.

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.

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.