Checking a column for a single cell that meets all conditions

jjg279

I have a form that has two multi-select columns and I am trying to find a way to create a chart out of the results. The two multi-selects are "Name" and "Food Given" and then a column for the number of days since the food was offered.

I want to take those results to another sheet and have it determine if each critter can get that food again (there are time limits for how often each food can be given, Broccoli daily, Berries only after two days etc…). Right now I am using a "Join" so I only have to search one column, and was using several "Contains" + "And", but my results are searching the whole column, I need it to only return "Yes" or "No" if all the conditions are met in a single cell.

Into:

DKazatsky2

Hi @jjg279,

Give this a try.

=IF(COUNT(COLLECT({Animal Name}, {Animal Name}, HAS(@cell, $[Primary Column]@row), {Food Given}, HAS(@cell, [Column2]$13))) > 0, "Yes", "No")

Hope this helps,

Dave

jjg279

Ah-ha!! That's got it!! Thank you Dave! I didn't know how to tie in the @cell and @row - will definitely use in future! Much mahalos!!

Michelle Choate 2

This would be a combination of IF, AND, and HAS statements together into one formula. I would write out what you are looking to have returned by each formula, then reverse engineer your formula from there.

jjg279

Hey Michelle! HAS was a great suggestion, hadn't worked with that one before, but I still wasn't able to get the formula to do what I needed. Still returning a positive answer if the name and fruit appears anywhere in the column, not in a single cell. Here was was I started with using HAS:

This was what I started with:

where "Gathered_joined" is a column containing a JOIN of all the results separated by commas:

I have found a less elegant solution using reports and a dashboard that I will run with for now, but any more insight on how to search a column for a single cell meeting all criteria would be very welcome!

DKazatsky2

Hi @jjg279,

Give this a try.

=IF(COUNT(COLLECT({Animal Name}, {Animal Name}, HAS(@cell, $[Primary Column]@row), {Food Given}, HAS(@cell, [Column2]$13))) > 0, "Yes", "No")

Hope this helps,

Dave

jjg279

Ah-ha!! That's got it!! Thank you Dave! I didn't know how to tie in the @cell and @row - will definitely use in future! Much mahalos!!

jjg279

Just in case anyone stumbles on this in future, in order to incorporate the time aspect I wrapped the formula provided by Dave in an OR and then repeated it for the required number of days. Final result was this:

I suspect there may be a more elegant way using < or > but I couldn't get it to work with HAS. Hope this is helpful! And thank you again Dave!