check occurrence of items in a range

  • Thread starter Thread starter Jack Sons
  • Start date Start date
J

Jack Sons

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 here:

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,

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.


<$$$$vram26@vsnl$$$.net$$$>
 
=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.
 
Back
Top