Check all items in one LIST to see if in another L...

simon_bailey1 · 05-30-2019 11:37 AM

Can I check a LIST to see if anything in another LIST is in it.
I’m checking in a volunteer rota to see if someone is already working at the times in the session they are being added to.
I have a LIST of times they are working in other sessions and a list of times in this session.

I’ve compared them by counting and seeing if they match, but that doesn’t work in a form, so doesn’t warn until synced.
Also I need to take 1 off one count as there is always a blank in one list, which is a pain.
If I can remove the blank I think I can remove the session times from the volunteer times and check if the LISTS match.

I’d really like to remove the volunteer from the REF dropdown if they are already working at the times in the session, but this is frying my brain, so I’m leaving that for next, but if that’s actually easier please suggest something.

Thanks

gpjoseph

This is what I need right now!!

simon_bailey1

@gpjoseph did you figure this out? I’m just coming back to it now.

Steve

Try ISNOTBLANK(INTERSECT(list1, list2)).

simon_bailey1

I’ve tried that, but it says both lists need to be the same type.
As far as i can see they are.
one list is a SELECT of the other list
SELECT(All Days[List Session Text Times 15 mins],[Volunteer]=[_THISROW].[Volunteer])

Do you know any reason it isn’t working?

Steve

INTERSECT() has some issues. This isn’t the first time I’ve seen this. I’d hoped it’d been fixed.

Try this:

ISNOTBLANK(
  INTERSECT(
    (LIST() + list1),
    (LIST() + list2)
  )
)

Gil

In general, you can subtract lists to remove the items in one list from the others (if they exist), fo example:
If A = {1, 3, 5} and B = {2,3} then:
A - B = {1, 5}
Using this concept (and some math) you can get the interception of 2 lists by using A - (A - B). In the example above:
A - (A - B) = A - {1, 5} = {3}
So the expression ISBLANK(A - (A - B)) will let you know if the 2 lists have anything in common

Check all items in one LIST to see if in another LIST