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

[Anthony_Nel]

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

Thanks

[Aleksi_Alkio]

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]

Comparing two Virtual Columns of type LIST

[Aleksi_Alkio]

You’re welcome

[Anthony_Nel]

Converting to strings:

FIND([V_COL1]&"",[V_COL2]&"")>0

Seems to work fine

[Aleksi_Alkio]

Are they REF_ROWS?

[Anthony_Nel]

No they are custom Virtual columns

[Aleksi_Alkio]

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]

Yes, values are always unique, but in random order

[Aleksi_Alkio]

COUNT({2,7,5,3,6,1}-{3,7,2})=COUNT({2,7,5,3,6,1})-COUNT({3,7,2})

[Anthony_Nel]

Thanks Aleksi works great!