How to check previous cells for same data and return Y/N

[Emily]

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.

 

[Pete_UK]

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

 

[Emily]

That's exactly right. Sorry I wasn't clearer.

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

 

[John C]

Do you want it to just be flagged? Or would you prefer to prevent the entry
in the first place?

 

[Emily]

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]

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:$E2,$E3)+COUNTIF($L$2:$L2,$E3)+COUNTIF($S$2:$S2,$E3)>0,COUNTIF($E$2:$E3,$L3)+COUNTIF($L$2:$L2,$L3)+COUNTIF($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.

 

[Emily]

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]

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?

 

[Pete_UK]

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)+COUNTIF(A2:S2,L2))>2,
(COUNTIF(S:S,S2)+COUNTIF(L:L,S2)+COUNTIF(E:E,S2)+COUNTIF(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

 

[Emily]

Nope, there are some blank rows and blank cells dispersed throughout.

 

[John C]

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)+COUNTIF($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)+COUNTIF($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.

 

[Emily]

Pete and John,

Thank you for saving my butt.

-Emily

 

[Pete_UK]

Thanks for feeding back, Emily.

Pete