check occurrence of items in a range
 

Hi all,

More than 24 hours ago I posted a question in the thread Text formula, but
it seems not to arrive. Therefore I repeat my question he

R,

Thank you very much, it indeed works for D5:D16 , also for D5:E16 (what I
was looking for, more that one dimension).

What also works to check if "apple", "pear", "cherry"; "coconut" and
"apricot" each occur at least once in a given range is

=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5
but
=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5
does not work.

I got stuck with this problem, why does it work for a one column range but
not for a two column range? I really want it solved, also because the
list or array way (how should I call it?), with {......} is so elegant and
easier to use.

Can you or anybody out there in this august newsgroup help (and explain)?

Jack.

http://groups.google.co.uk/groups?th...40clear.net.nz

And RagDyeR responded to your message.

Jack Sons wrote:

Hi all,

More than 24 hours ago I posted a question in the thread Text formula, but
it seems not to arrive. Therefore I repeat my question he

R,

Thank you very much, it indeed works for D5:D16 , also for D5:E16 (what I
was looking for, more that one dimension).

What also works to check if "apple", "pear", "cherry"; "coconut" and
"apricot" each occur at least once in a given range is

=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5
but
=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5
does not work.

I got stuck with this problem, why does it work for a one column range but
not for a two column range? I really want it solved, also because the
list or array way (how should I call it?), with {......} is so elegant and
easier to use.

Can you or anybody out there in this august newsgroup help (and explain)?

Jack.

--

Dave Peterson

Dave,

My browser shows that after my post there are three posts from
R.VENKATARAMAN. I was thankful for them, but they are no answer to my
question (as I re-formulated in my new post). I see no answer from a
RagDyeR.

My problem remains that
=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5
will work and
=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5
will not.

The basic problem is that the formula works if the data is in one column.
When spread over more than one column, the formula fails. I do not
understand why (I thought I grasped how the first formula works, apparently
I do not understand it fully).

Please forgive my repeated asking for help, I really sow not yet an answer.

Jack.


$$$
"Dave Peterson" schreef in bericht
...
http://groups.google.co.uk/groups?th...40clear.net.nz

And RagDyeR responded to your message.

Jack Sons wrote:

Hi all,

More than 24 hours ago I posted a question in the thread Text formula,
but
it seems not to arrive. Therefore I repeat my question he

R,

Thank you very much, it indeed works for D5:D16 , also for D5:E16 (what I
was looking for, more that one dimension).

What also works to check if "apple", "pear", "cherry"; "coconut" and
"apricot" each occur at least once in a given range is

=COUNT(MATCH({"apple", "pear", "cherry";
"coconut","apricot"},D5:D16,0))=5
but
=COUNT(MATCH({"apple", "pear", "cherry";
"coconut","apricot"},D5:E16,0))=5
does not work.

I got stuck with this problem, why does it work for a one column range
but
not for a two column range? I really want it solved, also because the
list or array way (how should I call it?), with {......} is so elegant
and
easier to use.

Can you or anybody out there in this august newsgroup help (and explain)?

Jack.

--

Dave Peterson

=match() likes a single column or a single row.

So your match() won't work.

If you don't get a good answer here, you may want to post in
..worksheet.functions. They live for this kind of thing.



Jack Sons wrote:

Dave,

My browser shows that after my post there are three posts from
R.VENKATARAMAN. I was thankful for them, but they are no answer to my
question (as I re-formulated in my new post). I see no answer from a
RagDyeR.

My problem remains that
=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5
will work and
=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5
will not.

The basic problem is that the formula works if the data is in one column.
When spread over more than one column, the formula fails. I do not
understand why (I thought I grasped how the first formula works, apparently
I do not understand it fully).

Please forgive my repeated asking for help, I really sow not yet an answer.

Jack.

$$$
"Dave Peterson" schreef in bericht
...
http://groups.google.co.uk/groups?th...40clear.net.nz

And RagDyeR responded to your message.

Jack Sons wrote:

Hi all,

More than 24 hours ago I posted a question in the thread Text formula,
but
it seems not to arrive. Therefore I repeat my question he

R,

Thank you very much, it indeed works for D5:D16 , also for D5:E16 (what I
was looking for, more that one dimension).

What also works to check if "apple", "pear", "cherry"; "coconut" and
"apricot" each occur at least once in a given range is

=COUNT(MATCH({"apple", "pear", "cherry";
"coconut","apricot"},D5:D16,0))=5
but
=COUNT(MATCH({"apple", "pear", "cherry";
"coconut","apricot"},D5:E16,0))=5
does not work.

I got stuck with this problem, why does it work for a one column range
but
not for a two column range? I really want it solved, also because the
list or array way (how should I call it?), with {......} is so elegant
and
easier to use.

Can you or anybody out there in this august newsgroup help (and explain)?

Jack.

--

Dave Peterson

--

Dave Peterson