Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I check if data in an external data range is changed afte. | Excel Worksheet Functions | |||
How can I check if data in external data range is changed after re | Excel Discussion (Misc queries) | |||
named range refers to: in a chart | Excel Discussion (Misc queries) | |||
How do I check for duplications in a range of data (excel)? | Excel Worksheet Functions | |||
Why is it important to check the range placed in the SUM function. | Excel Worksheet Functions |