Cell References

[Gary Thomson]

This is the third time I have asked this question, please
help!!!

I have the following range in Sheet1:

A B C D E ....
1 Unit 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths b fgh
3 English e d m
4 Music x qaz j
5 P.E.
6 Geography
7 History
8 Drama
9 Science p
.. .
.. .

(Note: Any letter can appear in a cell EXCEPT "y", and
for the moment consider that each letter can only appear
once - i.e. there cannot be two cells that contain an "a")

And I also have the following range in sheet 2:

A B C D E ....
1 Unit 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths y
3 English y y
4 Music y y
5 P.E. y
6 Geography
7 History
8 Drama
9 Science y
.. .
.. .
.. .


I want to do the following test:

Find the cell reference in Sheet1 that contains an "a" in
it, then look in the corresponding cell in Sheet2 to see
if it contains a "y".

i.e. for the above example, "a" is found in cell C4 in
Sheet1, and there IS a "y" in cell C4 of Sheet2, so the
answer would be TRUE.

Also in the above example, "b" is found in cell B2 in
Sheet1, and there is NO "y" in cell B2 of Sheet2, so the
answer would be FALSE.

Also in the above example, "c" is not found in Sheet1, so
I want the answer returned to be FALSE (not #VALUE! or
anything).


If this can be done, how can I change the formula so that
if there are 2 cells that contain an "a", I can locate the
cell references of BOTH these cells, and check if their
corresponding cells in Sheet2 contain a "y"? (note that
in this case, I also have a "Number of Occurences" Column
which tells me how many times each letter appears in the
range).

Many Thanks

 

[J.E. McGimpsey]

One way:

Assuming your range in sheet1 is named "rng1" and your range in
sheet2 is named rng2, and the letterof interest is in J1:

=IF(COUNTIF(rng1,"*"&J1&"*"),
SUMPRODUCT(--(NOT(ISERR(SEARCH("*"&J1&"*",rng1)))),
--(rng2="y"))=COUNTIF(rng1,"*"&J1&"*"))

Will return TRUE only if the letter in J1 is found in rng1 and for
all the instances found in rng1, the corresponding cell in rng2
contains "y". Note that rng1 and rng2 must be exactly the same size.

"Gary Thomson"

 

[Jim]

=SUMPRODUCT((rng="y")*(rng="a"))

 

[J.E. McGimpsey]

that won't work for the example the OP used.

 

[Gary Thomson]

Ok great that formula works if there is only one "a" in
Range1.

However, there will be instances where there are two or
more "a"'s in Range 1, and for each "a" I want to test
whether that particular "a" has a corresponding "y" in
Range2.

Because I have a separate column which shows the number of
times "a" has appeared in Range1 (this column is
labelled "Number of Occurences", then I will be able to
search for the appropriate number of "a"'s.

For example, if "Number of Occurences"=1, then I will
search for 1 "a", and the formula will return TRUE if
there is a corresponding "y", and FALSE if there is not.

If "Number of Occurences"=2, then I want two formulas, the
first to search for the first occurence of "a", and return
TRUE or FALSE dependant on whether there is a
corresponding "y" in Range2, and a second formula (to be
entered in a different cell to the first formula) to
search for the second occurence of "a", and return TRUE or
FALSE dependant on whether there is a corresponding "y" in
Range2.

If "Number of Occurences"=3, then there will be 3
Formulas, and so on up to "Number of Occurences"=5. There
will never be more than 5 occurences of the one letter in
Range1.


i.e. suppose "a" appears twice, "b" appears once, and "c"
appears 3 times: I would have the following:

Letter No. of Occurences 1st 2nd 3rd 4th 5th
a 2 TRUE FALSE --- --- ---
b 1 FALSE --- --- --- ---
c 3 TRUE FALSE TRUE --- ---
. .
. .

 

[J.E. McGimpsey]

it would be helpful if you'd make a full specification in the first
post of the thread. For instance, using "BOTH" in

how can I change the formula so that if there are 2 cells that
contain an "a", I can locate the cell references of BOTH these
cells, and check if their corresponding cells in Sheet2 contain a
"y"?

rather than "EACH" indicated to me that you wanted a single return.

Fortunately, there's a trivial fix (but see below). Assuming your
results table is on yet another sheet:

=IF(COUNTIF(rng1,"*"&$A2&"*")>=(COLUMN()-2),
SUMPRODUCT(--(NOT(ISERR(SEARCH("*"&$A2&"*",rng1)))),
--(rng2="y"))>=(COLUMN()-2),"---")

(the Number of occurrences column isn't necessary to the formula,
but the positioning of the table is, e.g.: If the "1st" column is D,
instead of C, then change the (COLUMN()-2)s to (COLUMN()-3))

NOTE: using this technique, the "1st", "2nd",etc. designation is
strictly arbitrary - there's no identity between a particular cell
and the result. All the TRUEs will be listed first, followed by
FALSEs, followed by "---".

If you need them in a particular order, then you'd need a different
technique and you'd need to give more information to specify how to
tell which one should be 1st, 2nd, etc., (i.e., should the order be
by date?, by unit?).

 

[J.E. McGimpsey]

On item I forgot to mention - you can copy the formula down and
across as far as necessary.