Match a list with a list

AppSheet AppSheet Q&A
1

I want to create an expression that checks if any item in a list is in another llist. So for example:

Is LIST(A,B,C) present in LIST(A,B,C) = TRUE

Is LIST(A,B,C) present in LIST(C,D,E) = TRUE

Is LIST(A,B,C) present in LIST(D,E,F) = FALSE

2

The number of the items in the list expression is static, i.e. always 3 or dynamic? Can you expect the number of the list?
If you have cap or max bar of number of the list items, then index expression should help.

I mean something like this? What do you think?

OR

(

index([SomeListCoumn 1],1) = index([SomeListColumn 2],1),

index([SomeListCoumn 1],1) = index([SomeListColumn 2],2),

index([SomeListCoumn 1],1) = index([SomeListColumn 2],3),

index([SomeListCoumn 1],2) = index([SomeListColumn 2],1),

index([SomeListCoumn 1],2) = index([SomeListColumn 2],2),

index([SomeListCoumn 1],2) = index([SomeListColumn 2],3),

index([SomeListCoumn 1],3) = index([SomeListColumn 2],1),

index([SomeListCoumn 1],3) = index([SomeListColumn 2],2),

index([SomeListCoumn 1],3) = index([SomeListColumn 2],3)

)

1 Like
3

The number of items in both lists are entirely dynamic and both are ENUMLISTs

4

Sure, but you can not expect what would be the reasonably max number of the items? If 10 is max, then I will repeat the same method of formula for another 7 times, even max is 100.
If the number of items are ever green, then my options wont work off course.

5

Add the two lists together and count the values (this will count duplicates, or values in both lists).

Then get the UNIQUE() values from that list and count again, then compare the counts and see if any were removed.

3 Likes
6

Smart Idea!

7

Hi Simon,

Is the requirement necessariliy that if there is even a single item common in the two lists , the logic should return a true and that each list can be of different length? Sometimes list one number of items can be can be bigger than that in list twotwo and vice versa and soemtimes these are equal?

8

Yes, nice approach.

9

Hi @Suvrutt_Gurjar

Yes the lists can be of different lengths. And Yes if one item matches that that shoudl return TRUE. If it helps, these are TEXT based lists

10

Hi Simon, I believe @GreenFlux s idea should work. Did you test it?
I was just curious how it works.

1 Like
11

One simple solution is COUNT(INTERSECT({A,B,C},{C,D,E}))>0

4 Likes
12

Good call, @Aleksi. I forgot we have an INTERSECT() function in AppSheet!

1 Like
13

Hi @GreenFlux, Exactly, I also forgot about INTERSECT() function.

Hi @Aleksi,

This is typical Aleksi’s master stroke.

You alway utilize the most apporpriate functions to meet the logic need in the most efficient manner.

14

Awesome @Aleksi that should work fine

But can someone at Appsheet add INTERSECT() to - https://help.appsheet.com/en/articles/961274-list-expressions-and-aggregates as I’d never heard of it!

3 Likes
15

You’re welcome

1 Like
16

Every day we need to learn new stuff which is good!

I never heard intersect function. Appreciate if this is documented with use cases.

@Aleksi

17

This function is very new… though I requested the article.

18

I came here to also suggest the Intersect function. But I only just noticed it yesterday.

19

https://help.appsheet.com/en/articles/3483429-intersect

2 Likes
20

Thank you Aleksi, for sharing this.
Better to put it on FEATURE FRIDAY!
This is features having tons of impact to improve Apps in various use cases.

Thank you again.

1 Like