Please help - how do you test if any of the i...

(Anthony Nel) #1

Please help - how do you test if any of the items in an unsorted list of values in list are in another list ?

i.e if any of the values in the list {3,7,2}

are in the list {2,7,5,3,6,1,3,2} ?

IN or CONTAINS seem to only match the first item in the list


(Aleksi Alkio) #2

Is your case exactly like this or is this just an example?

Are you reading values with the SELECT or like TableName[ColumnName]?

(Anthony Nel) #3

Comparing two Virtual Columns of type LIST

(Aleksi Alkio) #4

You’re welcome

(Anthony Nel) #5

Converting to strings:


Seems to work fine

(Aleksi Alkio) #6

Are they REF_ROWS?

(Anthony Nel) #7

No they are custom Virtual columns

(Steven Coile) #8
(Aleksi Alkio) #9

I believe your formula would not work correctly. It will only work if the order is exactly the same. If your lists would have only unique values like {2,7,5,3,6,1}, we could check the situation.

(Anthony Nel) #10

Yes, values are always unique, but in random order

(Aleksi Alkio) #11


(Anthony Nel) #12

Thanks Aleksi works great!