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

Anthony_Nel
Participant IV

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

0 11 322
11 REPLIES 11

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
Participant IV

Comparing two Virtual Columns of type LIST

Youโ€™re welcome

Anthony_Nel
Participant IV

Converting to strings:

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

Seems to work fine

Are they REF_ROWS?

Anthony_Nel
Participant IV

No they are custom Virtual columns

 

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
Participant IV

Yes, values are always unique, but in random order

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

Anthony_Nel
Participant IV

Thanks Aleksi works great!

Top Labels in this Space